SQL Server: Dynamic query with IN clause

[edburdo]

I have a query (several of them) that need to use the IN clause. This IN clause is somewhat dynamic. I have two possible combinations.

SELECT * FROM myTable WHERE myField IN ('A','B','C')

or

SELECT * FROM myTable WHERE myField IN ('X','Y')

I would like to use stored procedures, but I don't want to use the EXEC @sql function if I can help it. These queries are executed over and over again, so having a execute plan would help speed things up.

The other idea, is to just have two stored procs. Identical except for one always uses the 'X','Y' for the IN, and the other uses the 'A','B','C' approach.

Any suggestions?

[Phil Weber]

Eric: It is not possible to use a parameter as input to the IN clause. I would probably write a function to parse a comma-delimited parameter and insert the individual items into a temp table, then use a JOIN on that table instead of an IN clause. You might also consider passing the list as XML and using OPENXML to shred the XML into a temp table.

Or, you could just create two stored procs. ;-)

[edburdo]

Ok. For now, I'll use the dynamic queries (sql in the code). I'll try the other options and see what works best for each case. I have several places to make the distinction.

Thanx!

[Wiseman82]

Some basic examples. I don't think you'll have a problem with the caching on the execution plan if you opt for dynamic sql on SQL Server 2000. Query plans for ad-hoc queries are automatically parameterized and cached.
I prefer to avoid dynamic SQL unless it’s absolutely necessary. You need to be aware that the dynamic SQL executes as the user – you might need to grant permissions to underlying tables. You should also provide additional validation of user input to prevent SQL injection attacks and user error.



-- Example1 --
exec pInTest 1
exec pInTest 2
-- Example2 --
exec pInTest2 1
exec pInTest2 2
-- Example3 --
exec pInTest3 '''A'',''B'',''C'''
exec pInTest3 '''X'',''Y'''

-- Example1 --
create proc pInTest(@In tinyint)
as
if @In = 1
SELECT * FROM myTable WHERE myField IN ('A','B','C')
if @In=2
SELECT * FROM myTable WHERE myField IN ('X','Y')

-- Example2 --
create proc pInTest2(@In tinyint)
as
SELECT * FROM myTable WHERE (
(myField IN ('A','B','C') and @In=1)
or (myField IN ('X','Y') and @In=2))

-- Example3 --
create proc pInTest3(@In varchar(50))
as
declare @query nvarchar(4000)
set @query = 'SELECT * FROM myTable WHERE myField IN(' + @In + ')'
exec sp_executesql @query