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?

Answer 1

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

Answer 2

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

Answer 3

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

Note: I haven't done any sanitation on this query, please be aware that it's vulnerable to SQL injection. Clean as required.

Answer 4

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://learn.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 <= 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

Answer 5

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.

Answer 6

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

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

Answer 7

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)

Answer 8

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)