CODEWITHSUNDEEP
sqlsql-server
Preki
Preki

Reputation: 59

Execute multiple values to variable in stored procedure in SQL Server

I am trying to add multiple value to a parameter of a SQL Server stored procedure.

The name of the stored procedure is gpCompRes, parameter is @ProgramID

I need to add 200 + integers to ProgramID and I tried something like this

CREATE TYPE Programs as TABLE (Programs INT)
GO

DECLARE @ProgramID Programs INT

INSERT INTO @ProgramId  
VALUES (12071), (66306)

EXEC gpCompRes @ProgramId = Programs, 
               @OrganisationId = 1122,
               @UserIdList = 3326,
               @ActionUserId = 2255;
GO

I tried another option from the other stack flow which is also not working in my case to just declare and insert values but it did not work.

Thanks

Upvotes: 0

Views: 1686

Answers (1)

Gabriele Franco
Gabriele Franco

Reputation: 899

Programs is a user define table type. Below the correct script.

DROP TYPE IF EXISTS Programs
GO — From SQL 2016

CREATE TYPE Programs 
AS TABLE (Program INT)
GO

DECLARE @ProgramIDs Programs — You must define @ProgramIDs variable as “Programs” type

INSERT INTO @ProgramIDs
VALUES (12071), (66306)

EXEC gpCompRes 
           @ProgramId = @ProgramIDs,
           @OrganisationId = 1122,
           @UserIdList = 3326,
           @ActionUserId = 2255
GO

If you will use it in a program I advise you to add a PRIMARY KEY and NOT NULL CONSTRAINT in TABLE TYPE definition in order to avoid duplicates and NULL values.

DROP TYPE IF EXISTS Programs
GO

CREATE TYPE Programs AS TABLE (Program INT NOT NULL PRIMARY KEY CLUSTERED)
GO

For high-performance applications you can also convert it in IN-MEMORY TABLE TYPE as below:

DROP TYPE IF EXISTS Programs
GO

CREATE TYPE Programs AS TABLE (Program INT NOT NULL PRIMARY KEY CLUSTERED)
WITH ( MEMORY_OPTIMIZED = ON ) -From SQL Server 2014

Documentation:

TABLE TYPE

MEMORY OPTIMIZED TABLE TYPE

Upvotes: 1

Related Questions