Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

How do I use a variable in an IN clause?

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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...