How do I use a variable in an IN clause?

2

April 8, 2013 by Kenneth Fisher

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.

About these ads

2 thoughts on “How do I use a variable in an IN clause?

  1. tsiotas says:

    The 1st workaround would be
    LIKE ‘%,’+CAST(Id AS varchar)+’,%’
    not
    LIKE ‘,%’+CAST(Id AS varchar)+’,%’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 475 other followers

%d bloggers like this: