Execute multiple values to variable in stored procedure in SQL Server

Question

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

Is it purely because you have Declare @ProgramID Programs INT? You have 2 datatypes there. Programs and int. Remove the INT: Declare @ProgramID Programs; — Thom A
– Thom A ♦, Commented Jul 13, 2020 at 11:49
It gives me an error Msg 219, Level 16, State 1, Line 2 The type 'Programs' already exists, or you do not have permission to create it. Msg 102, Level 15, State 1, Line 7 Incorrect syntax near 'INT'. Msg 1087, Level 15, State 2, Line 8 Must declare the table variable "@ProgramId". — Preki
– Preki, Commented Jul 13, 2020 at 12:10
Well, if the TYPE Programs` already exists, you can't CREATE it, @Preki , that much is obvious. Considering that the Stored procedure gpCompRes will already have Programs as the datatype for the parameter @ProgramID then then would suggest it does already exist. And then, for the latter, as I said, you haveProgams INT; the INT shouldn't be there. Seems like a typographical error. — Thom A
– Thom A ♦, Commented Jul 13, 2020 at 12:13

Gabriele Franco · Accepted Answer · 2020-07-13 13:06:32Z

1

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

edited Jul 13, 2020 at 13:06

answered Jul 13, 2020 at 12:10

Gabriele Franco

9097 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Preki Over a year ago

that worked with some additional error messages Msg 219, Level 16, State 1, Line 1 The type 'Programs' already exists, or you do not have permission to create it. (2 rows affected) Msg 8114, Level 16, State 1, Procedure gpCompRes, Line 0 [Batch Start Line 2] Error converting data type nvarchar to int. But I will take a look.Maybe it should be the best to drop this table at the end

Gabriele Franco Over a year ago

Try again please

Collectives™ on Stack Overflow

Execute multiple values to variable in stored procedure in SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related