Hi there,
Unfortunately, you can't do that directly. The IN clause expects a comma separated list of values, not a string that holds a list of comma separated values.
There are a couple of ways around this. One solution is to build the SQL statement dynamically, and then use EXEC to execute it against the database. Another solution is to parse the string, insert the values in a temp table and then join that table with your original table.
Take a look here for a more detailed discussion on the options you have available:
http://vyaskn.tripod.com/passing_arr...procedures.htm
Another solution that has been posted to this list (the temp table solution):
Code:
CREATE FUNCTION Inlist (@list varchar(8000))
RETURNS @tbl TABLE (val int not null) AS
BEGIN
Declare @index int,
@pos int,
@str varchar(8000),
@num int
Set @pos = 1
Set @index = 1
While @index > 0
Begin
set @index = charindex(',', @list, @pos)
if @index > 0
Set @str = substring(@list, @pos, @index - @pos)
Else
Set @str = substring(@list, @pos, Len(@list))
Set @str = ltrim(rtrim(@str))
Set @num = cast(@str as integer)
Insert @tbl (val) values (@num)
Set @pos = @index + 1
End
Return
End
--------------------------------
Used in a select statement as followed:
"SELECT * FROM MyTable WHERE ID NOT IN (select val from inlist('1,2,3'))"
HtH,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.