Post reply

SP - CSV String as input parameter

  • January 8, 2006 at 6:43 pm

    #166677

    Hi, All,

    I have a stored Procedure being called from a Crystal Report that receives several parameters.  On of which is a string containing a list of values.  e.g. 'v1, v2, v3'

    The stored procedure need to perform a select at the end which would look like:

    Select * from table where fld in @sList

    Where @sList is 'v1, v2, v3'

    Thanks in advance for the help,

    Bill

  • January 8, 2006 at 7:41 pm

    #613449

    declare @sList varchar(100), @sqlcmd nvarchar(1000)

    select @sList = 'v1,v2,v3'

    select @sqlcmd = 'select * from table where fld in ' + replace('(''' + @sList + ''')', ',', ''',''')

    exec (@sqlcmd)

  • January 9, 2006 at 2:01 am

    #613471

    Split the values using a function such as the following:

    CREATE FUNCTION dbo.fnListToSet (@list VARCHAR(8000), @delimiter VARCHAR(3))

    RETURNS TABLE

    AS

    RETURN

    SELECT s.tuple

    FROM (

    SELECT SUBSTRING(

    @delimiter + @list + @delimiter

    , numbers.n + LEN(@delimiter)

    , CHARINDEX(@delimiter, @delimiter + @list + @delimiter, numbers.n + LEN(@delimiter)) - numbers.n - LEN(@delimiter)

    ) AS tuple

    FROM (

    SELECT D0.d*1 + D1.d*10 + D2.d*100 + D3.d*1000

    FROM (SELECT 0

    UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6

    UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D0 (d)

    , (SELECT 0

    UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6

    UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D1 (d)

    , (SELECT 0

    UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6

    UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D2 (d)

    , (SELECT 0

    UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6

    UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D3 (d)

    ) AS numbers (n)

    WHERE SUBSTRING(

    @delimiter + @list + @delimiter

    , numbers.n

    , LEN(@delimiter)

    ) = @delimiter

    AND n 0

    GO

    Now you can do:

    SELECT foo.* FROM foo

    INNER JOIN (SELECT * FROM dbo.fnListToSet(@sList, ',')) bar

    ON foo.fld = bar.tuple

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • January 9, 2006 at 7:22 am

    #613528

    Nicely done...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 9, 2006 at 7:56 am

    #613539

    Thank you all for you replies,

    Bill

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

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