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