Define variable to use with IN operator T-SQL

Question

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

User · Answer

DECLARE  mylist TABLE  Id int  INSERT INTO  mylist SELECT id FROM  VALUES  1   2   3   4   5   AS tbl id   SELECT   FROM Mytable WHERE theColumn IN  select id from  mylist

User · Answer

There are two ways to tackle dynamic csv lists for TSQL queries   1  Using an inner select  SELECT   FROM myTable WHERE myColumn in  SELECT id FROM myIdTable WHERE id  gt  10    2  Using dynamically concatenated TSQL  DECLARE  sql varchar max    declare  list varchar 256    select  list    1 2 3    SELECT  sql    SELECT   FROM myTable WHERE myColumn in       list        exec sp executeSQL  sql   3  A possible third option is table variables  If you have SQl Server 2005 you can use a table variable  If your on Sql Server 2008 you can even pass whole table variables in as a parameter to stored procedures and use it in a join or as a subselect in the IN clause   DECLARE  list TABLE  Id INT   INSERT INTO  list Id  SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4   SELECT        FROM      myTable     JOIN  list l ON myTable myColumn   l Id  SELECT        FROM      myTable WHERE     myColumn IN  SELECT Id FROM  list

User · Answer

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 · Answer

As no one mentioned it before  starting from Sql Server 2016 you can also use json arrays and OPENJSON  Transact-SQL    declare  filter nvarchar max      1 2    select   from dbo Test as t where     exists  select   from openjson  filter  as tt where tt  value    t id    You can test it in sql fiddle demo  You can also cover more complicated cases with json easier - see Search list of values and range in SQL using WHERE IN clause with SQL variable

User · Answer

I think you ll have to declare a string and then execute that SQL string   Have a look at sp executeSQL

User · Answer

If you want to do this without using a second table  you can do a LIKE comparison with a CAST   DECLARE  myList varchar 15  SET  myList     1 2 3 4    SELECT   FROM myTable WHERE  myList LIKE        CAST myColumn AS varchar 15            If the field you re comparing is already a string then you won t need to CAST   Surrounding both the column match and each unique value in commas will ensure an exact match   Otherwise  a value of 1 would be found in a list containing   4 2 15

User · Answer

No  there is no such type  But there are some choices    Dynamically generated queries  sp executesql  Temporary tables Table-type variables  closest thing that there is to a list  Create an XML string and then convert it to a table with the XML functions  really awkward and roundabout  unless you have an XML to start with    None of these are really elegant  but that s the best there is

User · Answer

DECLARE  StatusList varchar MAX   SET  StatusList  1 2 3 4   DECLARE  Status SYS INTEGERS  INSERT INTO   Status  SELECT Value  FROM dbo SYS SPLITTOINTEGERS FN  StatusList        SELECT Value From  Status

User · Answer

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

User · Answer

DECLARE  myList TABLE  Id BIGINT  INSERT INTO  myList Id  VALUES  1   2   3   4   select   from myTable where myColumn in select Id from  myList    Please note that for long list or production systems it s not recommended to use this way as it may be much more slower than simple INoperator like someColumnName in  1 2 3 4   tested using 8000  items list

User · Answer

Use a function like this   CREATE function  dbo   list to table    list varchar 4000   returns  tab table  item varchar 100   begin  if CHARINDEX      list    0 or CHARINDEX      list  is null begin     insert into  tab  item  values   list       return  end   declare  c pos int  declare  n pos int  declare  l pos int   set  c pos   0  set  n pos   CHARINDEX      list  c pos    while  n pos  gt  0 begin     insert into  tab  item  values  SUBSTRING  list  c pos 1  n pos -  c pos-1        set  c pos    n pos      set  l pos    n pos      set  n pos   CHARINDEX      list  c pos 1   end   insert into  tab  item  values  SUBSTRING  list  l pos 1 4000     return  end    Instead of using like  you make an inner join with the table returned by the function   select   from table 1 where id in   a   b   c     becomes  select   from table 1 a inner join  dbo   list to table    a b c   b on  a id   b item    In an unindexed 1M record table  the second version took about half the time     cheers

User · Answer

DECLARE  MyList TABLE  Value INT  INSERT INTO  MyList VALUES  1  INSERT INTO  MyList VALUES  2  INSERT INTO  MyList VALUES  3  INSERT INTO  MyList VALUES  4   SELECT   FROM MyTable WHERE MyColumn IN  SELECT Value FROM  MyList

User · Answer

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  gt 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  gt 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  gt 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 · Answer

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

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

Examples related to sql

Examples related to tsql