SQL variable to hold list of integers

Sql ServerListTsqlVariablesReporting Services

Sql Server Problem Overview


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?

Sql Server Solutions


Solution 1 - Sql Server

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

Solution 2 - Sql Server

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

Solution 3 - Sql Server

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

Solution 4 - Sql Server

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 <= 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

Solution 5 - Sql Server

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.

Solution 6 - Sql Server

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

Solution 7 - Sql Server

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)

Solution 8 - Sql Server

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)

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionErickTreetopsView Question on Stackoverflow
Solution 1 - Sql ServerslavooView Answer on Stackoverflow
Solution 2 - Sql ServerWilliam MuellerView Answer on Stackoverflow
Solution 3 - Sql ServerMöozView Answer on Stackoverflow
Solution 4 - Sql ServerRogalaView Answer on Stackoverflow
Solution 5 - Sql ServerErickTreetopsView Answer on Stackoverflow
Solution 6 - Sql ServerRavi AnandView Answer on Stackoverflow
Solution 7 - Sql ServerMax Alexander HannaView Answer on Stackoverflow
Solution 8 - Sql Serverthepirat000View Answer on Stackoverflow