In using a parameter in a stored procedure

  • I need to pass a comma delimited list of N single characters to a stored procedure and then retireve records that have a field equal to one of those values.

    Sample input @MemberStatus = 'M,N,F'

    Table data  MemberStatus M

    I tried Select Field1 from Table1 where MemberStatus IN (@MemberStatus) but it didn't work.

    Any ideas?

     

     

  • This will help you :

    Dynamic Search Conditions in T-SQL

  • Remi I don't belive you are suggesting Dynamic SQL

    How about this

    CREATE PROC MyProc

    (

    @pParam VARCHAR(2000)

    )

    /* Parse the Params */

    DECLARE @TblParam TABLE

    (

    Param VARCHAR(25)

    )

    WHILE CHARINDEX(',', @pParam) > 0

    BEGIN

    INSERT @TblParam VALUES(SUBSTRING(@pParam,1,CHARINDEX(',', @pParam) -1))

    SELECT @pParam = SUBSTRING(@pParam, CHARINDEX(',', @pParam) + 1, LEN(@pParam) - CHARINDEX(',', @pParam))

    END

    SELECT *

    FROM Table1

    JOIN

     @TblParam A

    ON Table1.MemberStatus = A.Param

    Regards,
    gova

  • I'm not... I thaught that there was a refference to the split function.. There is but it's not so easy to find out.

    BTW the charindex is about the slowest method to do this...

    Declare @ids as varchar(10)

    set @Ids = '1,2'

    Select * from dbo.SysObjects where id in (Select Element from dbo.Split(@Ids, ','))

    CREATE FUNCTION [dbo].[Split] (@vcDelimitedString nVarChar(4000),

    @vcDelimiternVarChar(100) )

    /**************************************************************************

    DESCRIPTION: Accepts a delimited string and splits it at the specified

    delimiter points. Returns the individual items as a table data

    type with the ElementID field as the array index and the Element

    field as the data

    PARAMETERS:

    @vcDelimitedString- The string to be split

    @vcDelimiter- String containing the delimiter where

    delimited string should be split

    RETURNS:

    Table data type containing array of strings that were split with

    the delimiters removed from the source string

    USAGE:

    SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID

    AUTHOR:Karen Gayda

    DATE: 05/31/2001

    MODIFICATION HISTORY:

    WHODATEDESCRIPTION

    ----------------------------------------------------------------

    ***************************************************************************/

    RETURNS @tblArray TABLE

    (

    ElementIDsmallintIDENTITY(1,1), --Array index

    ElementnVarChar(1200)--Array element contents

    )

    AS

    BEGIN

    DECLARE

    @siIndexsmallint,

    @siStartsmallint,

    @siDelSizesmallint

    SET @siDelSize= LEN(@vcDelimiter)

    --loop through source string and add elements to destination table array

    WHILE LEN(@vcDelimitedString) > 0

    BEGIN

    SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)

    IF @siIndex = 0

    BEGIN

    INSERT INTO @tblArray (Element) VALUES(@vcDelimitedString)

    BREAK

    END

    ELSE

    BEGIN

    INSERT INTO @tblArray (Element) VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))

    SET @siStart = @siIndex + @siDelSize

    SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)

    END

    END

    RETURN

    END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply