SQL variable to hold list of integers

Question

I m trying to debug someone else s SQL reports and have placed the underlying reports query into a query windows of SQL 2012   One of the parameters the report asks for is a list of integers   This is achieved on the report through a multi-select drop down box   The report s underlying query uses this integer list in the where clause e g   select   from TabA where TabA ID in   listOfIDs    I don t want to modify the query I m debugging but I can t figure out how to create a variable on the SQL Server that can hold this type of data to test it   e g    declare  listOfIDs int set listOfIDs    1 2 3 4   There is no datatype that can hold a list of integers  so how can I run the report query on my SQL Server with the same values as the report

User · Answer

Assuming the variable is something akin to:

CREATE TYPE [dbo].[IntList] AS TABLE(
[Value] [int] NOT NULL
)

And the Stored Procedure is using it in this form:

ALTER Procedure [dbo].[GetFooByIds]
    @Ids [IntList] ReadOnly
As

You can create the IntList and call the procedure like so:

Declare @IDs IntList;
Insert Into @IDs Select Id From dbo.{TableThatHasIds}
Where Id In (111, 222, 333, 444)
Exec [dbo].[GetFooByIds] @IDs

Or if you are providing the IntList yourself

DECLARE @listOfIDs dbo.IntList
INSERT INTO @listofIDs VALUES (1),(35),(118);

User · Answer

Table variable  declare  listOfIDs table  id int   insert  listOfIDs id  values 1   2   3        select   from TabA where TabA ID in  select id from  listOfIDs    or  declare  listOfIDs varchar 1000   SET  listOfIDs     1 2 3    --in this solution need put coma on begin and end  select   from TabA where charindex       CAST TabA ID as nvarchar 20           listOfIDs   gt  0

User · Answer

I use this    1-Declare a temp table variable in the script your building   DECLARE  ShiftPeriodList TABLE id INT NOT NULL     2-Allocate to temp table   IF  SOME CONDITION   BEGIN          INSERT INTO  ShiftPeriodList SELECT ShiftId FROM  hr   tbl WorkShift  END IF  SOME CONDITION2  BEGIN     INSERT INTO  ShiftPeriodList         SELECT  ws ShiftId         FROM  hr   tbl WorkShift  ws         WHERE ws WorkShift    Weekend VSD   OR ws WorkShift    Weekend SDL    END   3-Reference the table when you need it in a WHERE statement    INSERT INTO SomeTable WHERE ShiftPeriod IN  SELECT   FROM  ShiftPeriodList

User · Answer

For SQL Server 2016  and Azure SQL Database  the STRING SPLIT function was added that would be a perfect solution for this problem   Here is the documentation   https   docs microsoft com en-us sql t-sql functions string-split-transact-sql  Here is an example     List of ids in a comma delimited string   Note  the    WAITFOR DELAY   00 00 02    is a way to verify that your script          doesn t allow for SQL injection   DECLARE  listOfIds VARCHAR MAX     1 3 a 10 1   WAITFOR DELAY   00 00 02      --Make sure the temp table was dropped before trying to create it IF OBJECT ID  tempdb   MyTable   IS NOT NULL DROP TABLE  MyTable   --Create example reference table CREATE TABLE  MyTable   Id  INT NOT NULL    --Populate the reference table DECLARE  i INT   1  WHILE  i  lt   10  BEGIN     INSERT INTO  MyTable     SELECT  i       SET  i    i   1  END    Find all the values   Note  I silently ignore the values that are not integers   SELECT t  Id  FROM  MyTable as t     INNER JOIN           SELECT value as  Id           FROM STRING SPLIT  listOfIds               WHERE ISNUMERIC value    1   Make sure it is numeric               AND ROUND value 0    value   Make sure it is an integer    as ids     ON t  Id    ids  Id    --Clean-up DROP TABLE  MyTable    The result of the query is 1 3   Cheers

User · Answer

In the end i came to the conclusion that without modifying how the query works i could not store the values in variables   I used SQL profiler to catch the values and then hard coded them into the query to see how it worked   There were 18 of these integer arrays and some had over 30 elements in them   I think that there is a need for MS SQL to introduce some aditional datatypes into the language   Arrays are quite common and i don t see why you couldn t use them in a stored proc

User · Answer

You are right  there is no datatype in SQL-Server which can hold a list of integers  But what you can do is store a list of integers as a string   DECLARE  listOfIDs varchar 8000   SET  listOfIDs    1 2 3 4     You can then split the string into separate integer values and put them into a table  Your procedure might already do this   You can also use a dynamic query to achieve the same outcome   DECLARE  SQL nvarchar 8000    SET  SQL    SELECT   FROM TabA WHERE TabA ID IN       listOfIDs        EXECUTE   SQL

User · Answer

There is a new function in SQL called string split if you are using list of string   Ref Link STRING SPLIT  Transact-SQL   DECLARE  tags NVARCHAR 400     clothing road  touring bike  SELECT value FROM STRING SPLIT  tags       WHERE RTRIM value   lt  gt        you can pass this query with in as follows   SELECT     FROM  dbo   yourTable    WHERE  strval IN  SELECT value FROM STRING SPLIT  tags       WHERE RTRIM value   lt  gt

User · Answer

You can t do it like this  but you can execute the entire query storing it in a variable   For example   DECLARE  listOfIDs NVARCHAR MAX          1 2 3   DECLARE  query NVARCHAR MAX          Select        From TabA      Where TabA ID in       listOfIDs        Exec   query

[sql-server] SQL variable to hold list of integers

Examples related to sql-server

Examples related to list

Examples related to tsql

Examples related to variables

Examples related to reporting-services