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

Answer 1

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)

Answer 2

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

Answer 3

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 Database Where x in (@var1,@var2,@var3);

Answer 4

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.

Answer 5

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

Answer 6

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