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

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.

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

If you want input comma separated string as input  amp  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  lt 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  gt 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

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

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