[thelist] Using a comma list in an SP- was: Ordering a recordset by an array

Anthony Baratta Anthony at Baratta.com
Tue Oct 14 18:32:25 CDT 2003


At 01:06 PM 10/14/2003, Joshua Olson wrote:

>Here's an sp to do about the same thing.  It takes the list, populates a
>temp table, the joins against it to get the ordering.  It doesn't choke on
>duplicates... you just get the record twice:

We ran into an issue the other day that we worked around, but I would like 
to throw out to the group for comment.

We wanted to pass a comma separated string to a Stored Procedure and use 
that in an "in" clause. e.g.

    exec usr_TestSP "1,2,3,4,5"

       CREATE PROCEDURE usr_TestSP
          @inList varchar(1000) = NULL
       AS

       SELECT Col1,Col2,Col3 from tblTableName where Col4 in (@inList)

What appears to happen is @inList is taken as a full string, instead of 
used as a comma separated list.

e.g.

    SELECT Col1,Col2,Col3 from tblTableName where Col4 in ("1,2,3,4,5")

instead of

    SELECT Col1,Col2,Col3 from tblTableName where Col4 in (1,2,3,4,5)

of course we then tried this

    exec usr_TestSP "'1','2','3','4','5'"

and that didn't work either.

I do understand the "why" so my question is, is it even possible to some 
how let the SQL engine to know that we wanted it to use the @inList as a 
comma separated list versus a monolithic string? I'd like to avoid breaking 
the @inList variable up, slapping it back together with the full SQL string 
and then using the EXEC @FullSQL to run the select. (If that makes any sense.)

-- 
Anthony Baratta
President
Keyboard Jockeys

"Conformity is the refuge of the unimaginative."



More information about the thelist mailing list