Define variable to use with IN operator (T-SQL)

user_135050 0
Viewed 230507

I have a Transact-SQL query that uses the IN operator. Something like this:

select * from myTable where myColumn in (1,2,3,4)

Is there a way to define a variable to hold the entire list "(1,2,3,4)"? How should I define it?

declare @myList {data type}
set @myList = (1,2,3,4)
select * from myTable where myColumn in @myList
tsql sql
16 Answers

Starting with SQL2017 you can use STRING_SPLIT and do this:

declare @myList nvarchar(MAX)
set @myList = '1,2,3,4'
select * from myTable where myColumn in (select value from STRING_SPLIT(@myList,','))
user_3957730

I know this is old now but TSQL => 2016, you can use STRING_SPLIT:

DECLARE @InList varchar(255) = 'This;Is;My;List';

WITH InList (Item) AS (
    SELECT value FROM STRING_SPLIT(@InList, ';')
)

SELECT * 
FROM [Table]
WHERE [Item] IN (SELECT Tag FROM InList)
user_73906860

slight improvement on @LukeH, there is no need to repeat the "INSERT INTO": and @realPT's answer - no need to have the SELECT:

DECLARE @MyList TABLE (Value INT) 
INSERT INTO @MyList VALUES (1),(2),(3),(4)

SELECT * FROM MyTable
WHERE MyColumn IN (SELECT Value FROM @MyList)
Admin1
user_17448340

This one uses PATINDEX to match ids from a table to a non-digit delimited integer list.

-- Given a string @myList containing character delimited integers 
-- (supports any non digit delimiter)
DECLARE @myList VARCHAR(MAX) = '1,2,3,4,42'

SELECT * FROM [MyTable]
    WHERE 
        -- When the Id is at the leftmost position 
        -- (nothing to its left and anything to its right after a non digit char) 
        PATINDEX(CAST([Id] AS VARCHAR)+'[^0-9]%', @myList)>0 
        OR
        -- When the Id is at the rightmost position
        -- (anything to its left before a non digit char and nothing to its right) 
        PATINDEX('%[^0-9]'+CAST([Id] AS VARCHAR), @myList)>0
        OR
        -- When the Id is between two delimiters 
        -- (anything to its left and right after two non digit chars)
        PATINDEX('%[^0-9]'+CAST([Id] AS VARCHAR)+'[^0-9]%', @myList)>0
        OR
        -- When the Id is equal to the list
        -- (if there is only one Id in the list)
        CAST([Id] AS VARCHAR)=@myList

Notes:

  • when casting as varchar and not specifying byte size in parentheses the default length is 30
  • % (wildcard) will match any string of zero or more characters
  • ^ (wildcard) not to match
  • [^0-9] will match any non digit character
  • PATINDEX is an SQL standard function that returns the position of a pattern in a string
user_62195750

Most of these seem to focus on separating-out each INT into its own parenthetical, for example:

  • (1),(2),(3), and so on...

That isn't always convenient. Especially since, many times, you already start with a comma-separated list, for example:

  • (1,2,3,...) and so on...

In these situations, you may care to do something more like this:

DECLARE @ListOfIds TABLE (DocumentId INT);

INSERT INTO @ListOfIds
SELECT Id FROM [dbo].[Document] WHERE Id IN (206,235,255,257,267,365)

SELECT * FROM @ListOfIds

I like this method because, more often than not, I am trying to work with IDs that should already exist in a table.

user_3123170

My experience with a commonly proposed technique offered here,

SELECT * FROM Mytable WHERE myColumn IN (select id from @mylist)

is that it induces a major performance degradation if the primary data table (Mytable) includes a very large number of records. Presumably, that is because the IN operator’s list-subquery is re-executed for every record in the data table. I’m not seeing any offered solution here that provides the same functional result by avoiding the IN operator entirely. The general problem isn’t a need for a parameterized IN operation, it’s a need for a parameterized inclusion constraint. My favored technique for that is to implement it using an (inner) join:

DECLARE @myList varchar(50) /* BEWARE: if too small, no error, just missing data! */
SET @myList = '1,2,3,4'

SELECT *
FROM myTable
JOIN STRING_SPLIT(@myList,',') MyList_Tbl
  ON myColumn = MyList_Tbl.Value

It is so much faster because the generation of the constraint-list table (MyList_Tbl) is executed only once for the entire query execution. Typically, for large data sets, this technique executes at least five times faster than the functionally equivalent parameterized IN operator solutions, like those offered here.

user_36855160
Related