How do I use a variable in an IN clause?


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’

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'

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.

 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.

16 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)+’,%’
    LIKE ‘,%’+CAST(Id AS varchar)+’,%’

  2. Rui Pinto says:

    Hi! The last method doesn’t work. The IN requires ‘xxxxx’ in every statement you pass 😉

  3. N Aung says:

    If we use table variable and in the main query where clause we could use exist or in …
    I think it would be simpler

  4. Toheeb Bello says:

    @PartnerName VARCHAR(40),
    @Owner NVARCHAR (255)

    ud.user_name LIKE CONCAT (‘%’, @PartnerName, ‘%’)

    OR so.SiteOwneEmail LIKE CONCAT(‘%’, @Owner, ‘%’)

  5. Thomas says:

    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.

  6. Sameer Pramod Shahakar says:

    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)

  7. Ameer says:

    Another workaround is to Declare a Table variable.
    DECLARE @AnyLIST ( myList Varchar(10))
    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 ‘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)

  8. Rafael says:

    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’.

Leave a Reply

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: