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?

Acting

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

well worded question. +1 – Akerley

Does this need the reporting-services tag? It seems independent of it. – Rikkiriksdag

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

Baltoslavic

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. – Acting

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? – Barbarity

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. – Fluorine

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. – Soudan

@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) – Metabolite

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

Hangman

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.

Handfast

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

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. – Scanner

@Scanner Agreed, users will need to do their own sanitation as required. – Disunion

@Handfast 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. – Scanner

@Handfast 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!! – Scanner

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

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

Scanner

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.

Acting

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

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. – Disunion

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

Crouton

Looks plausible but only SQL Server 2016 onwards unfortunately. – Chaille

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)

Standley

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)

Hallowed

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. – Scanner

Answer 9

We can declare table and using it.

DECLARE @myTableVariable TABLE (id INT)
insert into @myTableVariable values(1),(2),(3)
select * from @myTableVariable

Candi