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
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)
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;
I think you'll have to declare a string and then execute that SQL string.
Have a look at sp_executeSQL
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)
No, there is no such type. But there are some choices:
None of these are really elegant, but that's the best there is.
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 > 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)
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,','))
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)
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:
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?
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,'
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 > 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
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)
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)
Source: Stackoverflow.com