[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