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

[Joshua Olson]

----- Original Message ----- 
From: "Anthony Baratta" <Anthony at Baratta.com>
Sent: Tuesday, October 14, 2003 7:32 PM


> What appears to happen is @inList is taken as a full string, instead of
> used as a comma separated list.
>
> ...
>
> 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.)

You wouldn't need to break it up if your going to make a string out of it
and just execute the string dynamically.  Just concatenate it such as this:

Set @fullstr = 'SELECT Col1,Col2,Col3 from tblTableName where Col4 in (' +
@inList + ')'

Execute @fullstr

Careful with using the IN clause... some databases only allow you 255
literals in the IN clause... meaning, if the list has more than 255
characters then the query will crash when you execute it.

To answer your question, that is one of two ways that I know of... the other
of which I posted earlier.

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.210.0168