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?

Solution 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

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

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

Solution 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://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

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.

View more solutions

Comments

[paparazzo]

I know I have used TVP Table Value Parmeter to insert data but now sure if it can be used in a where. Sequel?

[RayLoveless]

well worded question. +1

[ErickTreetops]

Thanks for that but again it requires me to rewrite the way the variable is read in the query. I have to keep it the same.

[ErickTreetops]

Thanks, but again would need to modify a query which i'm not allowed to.

[John Saunders]

SQL Server doesn't need arrays, when it has table-valued parameters and variables.

[Möoz]

So what we know is that the query uses a list of integers (passed to it by an array?). What I don't understand is how your query was using them without utilising one of the given methods in the answers. Provide some more context and we can help you further.

[Rafael Moreira]

What if you don't know what the IDs are and that comes from a query? Example: SET @AddressIDs = (SELECT ID FROM address WHERE Account = 1234) This query will return multiple IDs and I get an error saying the subquery returned more than one result and that is not allowed. Is there anyway to create a variable that will store an array if IDs from a subquery?

[User M]

I tried the second option and it works for less number of records. When I increase the number of Ids, I get TimeOut error. Maybe cast is hampering the performance.

[Rogala]

If anyone uses this, please be aware that this could be very vulnerable to SQL injection if @listOfIDs is a string parameter provided by a user. Depending on your architecture of your app, this could or could not be an issue.

[Rogala]

As a stated in a previous comment, depending on how you implement this type of solution, be aware that this could be vulnerable to SQL injection if @listOfIDs is a parameter provided by a user.

[Möoz]

@Rogala Agreed, users will need to do their own sanitation as required.

[Rogala]

@Möoz I recommend adding a note to your answer to reflect this. Not everyone knows, and they simply copy and paste solutions without thinking about the consequences. Dynamic SQL is VERY dangerous, and I avoid it like that plague.

[Rogala]

@Möoz Also, I didn't down vote your answer, but what you please take a few minutes and check out my answer? The STRING_SPLIT function is pretty sweet, and I think you will be ALL over it!!

[Wade Hatler]

Not the answer to the original question but it's the answer to one I didn't ask, so I'm good. I pass in a List<int> as a parameter, but want to make a local variable for testing in SSMS. It's killer.

[AnotherFineMess]

Looks plausible but only SQL Server 2016 onwards unfortunately.

[user3127554]

I have the error Conversion failed when converting the varchar value '68077, 68136 , 68143' to data type int.

[Alfredo A.]

Great answer, saved me a lot of time

[HomeMade]

@RafaelMoreira that happens when you have a list of values returned instead of one value and you should use 'IN'insted of '='. In that case you would compare the values e.g. SELECT * FROM test WHERE ID IN (SELECT ID FROM IDtable)