Feeding an "IN" Clause

I know I can supply an explicit set to an IN clause, or I can feed it with a select statement. What other ways in TSQL 2005 can I feed it?

One thing I'd like to do is feed an IN clause by calling a stored procedure. I'm new to TSQL, so I don't know if I can do that. I'd also like to know if there is a way to pass a variadic list of values as a parameter to a stored procedure, so that they can be used in an "IN" clause found in that stored procedure. I think the way to do that last item is by having a recordset parameter. Is that correct? Any other way to do it?

You can use a static list of values for that:

IN (3,4,5)

HTh, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Yes, that syntax I already know, and is what I was calling an "explicit set." Its principally in the area of forming that set parametrically that I was after...such as through the "how to's" of stored procedures.|||

Based on another question you asked, you could try passing a comma delimited list of values to your stored procedure, and then use the LIKE functionality within a Case/When statement to ultimately achieve your goals.

Something like this...

Create Procedure IsValueIn(@.Value int, @.List VarChar(8000))
As
SET NOCOUNT ON

Select Case When ',' + @.List + ',' LIKE '%' + Convert(VarChar(20), @.Value) + '%'
Then 1
Else 0
End As ValueIn

Test it like this...

IsValueIn 10, '10,20,30'

IsValueIn 20, '10,20,30'

IsValueIn 30, '10,20,30'

IsValueIn 11, '10,20,30'

|||

My apologies for making a mistake. The method I presented in my previous post should work, but the implementation is a little off. The corrected code should be...

Alter Procedure IsValueIn(@.Value int, @.List VarChar(8000))
As
SET NOCOUNT ON

Select Case When ',' + @.List + ',' LIKE '%,' + Convert(VarChar(20), @.Value) + ',%'
Then 1
Else 0
End As ValueIn

Notice the extra commas added to the like compare. These commas are important because the code I posted earlier would return true for:

IsValueIn 1, '10,20,30'

With the code in this reply, the stored procedure correctly returns false. Again, my apologies for misleading you with my previous response.

|||

I would suggest using a user defined function. You can pass whatever you want, and break it up into a set of data that can be used like:

select *
from table
where tableId in (select value from dbo.myFunction(@.parameter))

A good resource for this sort of thing is Erland's article: http://www.sommarskog.se/arrays-in-sql.html

sql