My SQL code is fairly simple. I'm trying to select some data from a database like this:
SELECT * FROM DBTable
WHERE id IN (1,2,5,7,10)
I want to know how to declare the list before the select (in a variable, list, array, or something) and inside the select only use the variable name, something like this:
VAR myList = "(1,2,5,7,10)"
SELECT * FROM DBTable
WHERE id IN myList
6 Answers
You could declare a variable as a temporary table like this:
declare @myList table (Id int)
Which means you can use the insert statement to populate it with values:
insert into @myList values (1), (2), (5), (7), (10)
Then your select statement can use either the in statement:
select * from DBTable
where id in (select Id from @myList)
Or you could join to the temporary table like this:
select *
from DBTable d
join @myList t on t.Id = d.Id
And if you do something like this a lot then you could consider defining a user-defined table type so you could then declare your variable like this:
declare @myList dbo.MyTableType
That is not possible with a normal query since the in clause needs separate values and not a single value containing a comma separated list. One solution would be a dynamic query
declare @myList varchar(100)
set @myList = '1,2,5,7,10'
exec('select * from DBTable where id IN (' + @myList + ')')
You can convert the list of passed values into a table valued parameter and then select against this list
DECLARE @list NVARCHAR(MAX)
SET @list = '1,2,5,7,10';
DECLARE @pos INT
DECLARE @nextpos INT
DECLARE @valuelen INT
DECLARE @tbl TABLE (number int NOT NULL)
SELECT @pos = 0, @nextpos = 1;
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos;
END
SELECT * FROM DBTable WHERE id IN (SELECT number FROM @tbl);
In this example the string passed in '1,2,5,7,10' is split by the commas and each value is added as a new row within the @tbl table variable. This can then be selected against using standard SQL.
If you intend to reuse this functionality you could go further and convert this into a function.
Alternative to @Peter Monks.
If the number in the 'in' statement is small and fixed.
DECLARE @var1 varchar(30), @var2 varchar(30), @var3 varchar(30);
SET @var1 = 'james';
SET @var2 = 'same';
SET @var3 = 'dogcat';
Select * FROM DBTable Where x in (@var1,@var2,@var3);
I've always found it easier to invert the test against the list in situations like this. For instance...
SELECT
field0, field1, field2
FROM
my_table
WHERE
',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'
This means that there is no complicated mish-mash required for the values that you are looking for.
As an example, if our list was ('1,2,3'), then we add a comma to the start and end of our list like so: ',' + @mysearchlist + ','.
We also do the same for the field value we're looking for and add wildcards: '%,' + CAST(field3 AS VARCHAR) + ',%' (notice the % and the , characters).
Finally we test the two using the LIKE operator: ',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'.
If you want input comma separated string as input & apply in in query in that then you can make Function like:
create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end;
You can use it like :
Declare @Values VARCHAR(MAX);
set @Values ='1,2,5,7,10';
Select * from DBTable
Where id in (select items from [dbo].[Split] (@Values, ',') )
Alternatively if you don't have comma-separated string as input, You can try Table variable OR TableType Or Temp table like: INSERT using LIST into Stored Procedure
