String parameter

  • Good mornin all-

    I need to pass a parameter to a function that would be a comma delimited list of codes, to be used in a filter clause such as -

    @param = '81.80','81.82','81.85','82.20'

    Select x,y from z where x in (@param)

    What would be some methods of accomplishing this?

     

     

  • Not going into any details, as I would need more information regarding your stored prc, it looks like you will need to build your query in the stored procedure dynamically and execute it using sp_executesql or the execute statement.

    If you need additional help, please be prepared to post additional info regarding what you are trying to do.

  • See Erland Sommarskog's article on this subject at

    http://www.sommarskog.se/arrays-in-sql.html

    SQL = Scarcely Qualifies as a Language

  • I need to pick up all codes and descriptions for each user record. So the results would look like

    id     codes+desc

    1      81.51-desc8151,81.52-desc8152.....

     

    id from tableA, codes+desc from tableB. Because an id can have one to 30 codes, and I only want certain ones if they exists, and only one row per id. I'm looking to catatenate the codes.

     

     

     

  • For this I've used a function I found on a technet forum from Bruce L. (MVP) which parses the values in the string and returns a table that you join to. Here's what it looks like...

    /*********************** CODE ***********************/

    CREATE FUNCTION charlist_to_table(

    @list ntext,

    @delimiter nchar(1) = N',')

    RETURNS @tbl TABLE (

    listpos int IDENTITY(1, 1) NOT NULL,

    str varchar(4000),

    nstr nvarchar(2000)

    ) AS

    BEGIN

    DECLARE @pos int,

    @textpos int,

    @chunklen smallint,

    @tmpstr nvarchar(4000),

    @leftover nvarchar(4000),

    @tmpval nvarchar(4000)

    SET @textpos = 1

    SET @leftover = ''

    WHILE @textpos 0

    BEGIN

    SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))

    INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)

    SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))

    SET @pos = charindex(@delimiter, @tmpstr)

    END

    SET @leftover = @tmpstr

    END

    INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),ltrim(rtrim(@leftover)))

    RETURN

    END

    GO

    /*********************** CODE ***********************/

    /*********************** EXAMPLE ***********************/

    declare @STR varchar(4000)

    set @STR = 'phoenix, boston, chicago, denver, san diego'

    select

    *

    from

    UScities as c

    inner join charlist_to_table(@str, Default) as x on

    x.str = c.city

    /*********************** EXAMPLE ***********************/

    In this example there is no where clause becuase the inner join is handling this by comparing the 'city' field to all the values passed in by using our charlist_to_table function.

    Hope this Helps...

    -Ben


    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • This is a great example of how to use a numbers table in combination with a table valued split function.  Do a search on SSC for split or number table and you'll get plenty of info and examples.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

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

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