I see this question in one form or another a lot. I’ve seen this or something like it probably half a dozen times in the last couple of weeks on the forums I read. To the point where it even showed up in a dream the other night (I know, weird hu?). Just to be clear I’m talking about trying to do something like this.

DECLARE @InList varchar(100)
SET @InList = ‘1,2,3,4’
SELECT *
FROM MyTable
WHERE Id IN (@InList)

So to answer the title question, it doesn’t work. It can’t be made to work. Give it up. It would be nice if it did, but it doesn’t.

There, that’s over. Now that we are clear that this particular piece of code won’t work I know of 3 viable workarounds that I’m going to discuss.

1st – Using LIKE. This is by far the most restrictive of the three options. It will only really work well with numbers or strings where you can be sure that there won’t be a comma in the string. Another big drawback is speed. Because of the way it’s written you aren’t going to get much use out of indexes. The benefit to this method is it’s quick and easy to write. It’s primarily useful if you are writing an ad-hoc query with a small enough table that speed isn’t a huge issue.

 DECLARE @InList varchar(100)
SET @InList = '1,2,3,4'
SELECT *
FROM MyTable
WHERE ','+@InList+',' LIKE ',%'+CAST(Id AS varchar)+',%' 

2nd – Using a table-valued split function. This one is easy to write and can take advantage of indexes. The only real drawback is that you have to have a split function available. If you are interested Jeff Moden on sqlservercentral has a good one here. http://www.sqlservercentral.com/articles/Tally+Table/72993/

 DECLARE @InList varchar(100)
SET @InList = '1,2,3,4'
SELECT MyTable.*
FROM MyTable
JOIN DelimitedSplit8K (@InList,',') SplitString
ON MyTable.Id = SplitString.Item

You can also use split functions with CROSS APPLY if you have a column that has a delimited list. This is a bit off scope from what I’m discussing so I’m not going to put an example here but Jeff put a couple in his discussion of his split function.

3rd – Dynamic SQL. If you like dynamic SQL and use it on a regular basis then this one is nice and easy and certainly my favorite. If however dynamic SQL is not something that you feel comfortable with then you might just go with method #2.

 DECLARE @InList varchar(100)
SET @InList = '1,2,3,4'
DECLARE @sql nvarchar(1000)
SET @sql = 'SELECT * ' + 
'FROM MyTable ' + 
'WHERE Id IN ('+@InList+') '
EXEC sp_executesql @sql

One of these days Microsoft may allow variables in query statements like this but I’m not going to hold my breath. In the mean time these are the three methods I know of to handle a variable “IN” clause. There may be others and if you know one I would love to hear about it.

Filed under: Dynamic SQL, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, dynamic sql, language sql, microsoft sql server, split function, sql statements, T-SQL