SQL variable to hold list of integers

Table variable

declare @listOfIDs table (id int);insert @listOfIDs(id) values(1),(2),(3);    select *from TabAwhere 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 endselect *from TabAwhere charindex(',' + CAST(TabA.ID as nvarchar(20)) + ',', @listOfIDs) > 0

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] ReadOnlyAs 

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.IntListINSERT INTO @listofIDs VALUES (1),(35),(118);

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