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

User · Answer

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

User · Answer

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  gt  0 BEGIN     SELECT  nextpos   charindex       list   pos   1      SELECT  valuelen   CASE WHEN  nextpos  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Server procedure declare a list

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008-r2