SQL Server procedure declare a list

Question

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

Possible duplicate of http://stackoverflow.com/questions/6369/how-to-pass-a-comma-separated-list-to-a-stored-procedure — Paul, Jan 16 '14 at 13:39
^unrelated IMO, though duplicates certainly exist. [MySQL but might be helpful](http://stackoverflow.com/questions/327274/mysql-prepared-statements-with-a-variable-size-variable-list) — OGHaza, Jan 16 '14 at 14:06

score 146 · Accepted Answer · answered Jan 16 '14 at 11:43

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

Thanks! I used your solution in the end :) – Alex Doro Jan 16 '14 at 14:37 — Alex Doro, Jan 16 '14 at 14:37

juergen d · Answer 2 · 2022-01-31T16:46:12.053

14

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 + ')')

edited Jan 31 '22 at 16:46

answered Jan 16 '14 at 11:27

juergen d

201,996
37
293
362

Thanks! I was just about to ask you how to declare it correctly! I'll try this. – Alex Doro Jan 16 '14 at 11:32

score 6 · Answer 3 · answered Jan 16 '14 at 11:45

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.

Aaron C · Answer 4 · 2023-08-02T21:24:58.743

4

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);

edited Aug 02 '23 at 21:24

answered Sep 13 '18 at 04:29

Aaron C

301
1
8

score 3 · Answer 5 · answered Jan 16 '14 at 12:06

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) + ',%'.

score 2 · Answer 6 · edited May 23 '17 at 11:46

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

Does this work for all types? Do you not need to cast your output from the function to `INT`? — Paul, Jan 16 '14 at 13:30
@Westie, comparison must be in same types. So if id in dbtable is type int then yes, it needs cast. — Pranav Singh, Jan 16 '14 at 18:02

SQL Server procedure declare a list

6 Answers6

Linked