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
