How do I use a variable in an IN clause?
16April 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.
SQL Studies 
The 1st workaround would be
LIKE ‘%,’+CAST(Id AS varchar)+’,%’
not
LIKE ‘,%’+CAST(Id AS varchar)+’,%’
Got to love typo’s. Thanks! I’ll fix it now.
Hi! The last method doesn’t work. The IN requires ‘xxxxx’ in every statement you pass 😉
Not sure I understand what you mean?
I think what he is trying to say is that your dynamic SQL example works as long as the values in the series are numeric. However, if the values are characters or alphanumeric it fails: example SET @InList = ‘A,B,C,D’.
Yes and no. To do string values it would have to be @InList = ”’A”,”B”,”C”,”D”’
If we use table variable and in the main query where clause we could use exist or in …
I think it would be simpler
Absolutely. And if you have a large number of values then performance is going to be better using a temp table/table variable too.
@PartnerName VARCHAR(40),
@Owner NVARCHAR (255)
ud.user_name LIKE CONCAT (‘%’, @PartnerName, ‘%’)
OR so.SiteOwneEmail LIKE CONCAT(‘%’, @Owner, ‘%’)
That can work. But you can get some false positives. For example:
user_name = ‘Amy’ and @PartnerName = ‘Bob,Amy123’
The last one with Dynamic SQL works, but can be very dangerous because of SQL Injection:
DECLARE @InList varchar(100)
SET @InList = ‘1,2,3,4) OR (1=1’
DECLARE @sql nvarchar(1000)
SET @sql = ‘SELECT * ‘ +
‘FROM MyTable ‘ +
‘WHERE Id IN (‘+@InList+’) ‘
EXEC sp_executesql @sql
Now it will return all data and not only the rows with Id 1,2,3 or 4
So if @InList is a parameter for a Stored Procedure, be careful with this one.
Oh yea, certainly! I would never use that method unless I’m explicitly creating the list and there is no user input. So for example a series of dropdowns or check boxes where you are creating a delimited list in the code.
I have a use case where I need to use the variables to check the strings and a name as well as below:
set @old_status = char(39)+’STARTED’+CHAR(39)+’,’+CHAR(39)+’COMPLETED’+CHAR(39)
select * from [dbo].[JETABLE]
where user_id in (select id from [dbo].[User_Table] where (user_name=@user_name and is_deleted=0))
and activity_status in (@old_status)
But the above returns me a blank when I have records matching. If I replace @old_status with values, I get the required result. Kindly suggest.
Unfortunately the suggestions I gave in the above post are the only ones I know that will work. My favorite is to create a temp table, insert the rows into it and then do something like
activity_status IN (SELECT name FROM #status)
Hi
Another workaround is to Declare a Table variable.
DECLARE @AnyLIST ( myList Varchar(10))
INSERT INTO @AnyLIST
Values (‘First’), (‘Second’), (‘Third’), (‘Fourth’)
Then user this in you in statement.
Select * from myTable
Where myPosition in (Select myList from @AnyList)
If you ware using Azure Synapse it does not support Table variable till date. To work on Azure Synapse DWH you can you Temp table (#Table)
Same can be
SELECT * INTO #AnyList FROM
(Select ‘First’ as myList –Add a col name
Union Select ‘Second’
union Select ‘Third’
union select ‘Fourth’) list
This would create a temp table. and then you can use this in a same way. Then user this in you in statement.
Select * from myTable
Where myPosition in (Select myList from #AnyList)
If you have a split function you can simplify #2 by doing something like:
“WHERE id IN(SELECT DISTINCT ColumnName FROM #IdList)”
where ColumnName has the ids split from a string like ‘1,2,3,4’.