CODEWITHSUNDEEP
sqlsql-server
Capaj
Capaj

Reputation: 4206

MSSQL procedure to insert multiple values instead of one value

I have this MSSQL procedure:

CREATE PROCEDURE InsertCodeIfNotExist
    @Code nvarchar(32)
AS
  INSERT Codes (Code)
  SELECT @Code
  WHERE NOT EXISTS
          (SELECT 1
            FROM Codes
            WHERE Code = @Code
          );

Which works well for me, but I would be better if I could have it work with like a 100 values and call this procedure for each value. Is that possible with mssql? Can I define like a InsertManyCodesIfNotExist and loop over hundreds of values there?

Upvotes: 1

Views: 107

Answers (2)

dns_nx
dns_nx

Reputation: 3933

Yes, you can define a table-valued parameter and pass a table to your stored procedure:

/* Create a table type. */ 
CREATE TYPE CodeTableType AS TABLE ( Code VARCHAR(32)); 
GO 

/* Create a procedure to receive data for the table-valued parameter. */ 
CREATE PROCEDURE dbo.InsertCodeIfNotExist
@TVP CodeTableType READONLY 
AS 
SET NOCOUNT ON 
INSERT INTO Codes (Code) 
SELECT 
    Code
FROM  
    @TVP;

Details you can find here: https://msdn.microsoft.com/en-us/library/bb510489(v=sql.120).aspx

Upvotes: 0

Randy Minder
Randy Minder

Reputation: 48392

Yes you could do this quite easily a couple different ways. One way I handle situations like this is to create a user defined data type such as the one I use below:

CREATE TYPE [dbo].[ModuleList] AS TABLE(
    [SubId] [varchar](12) NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [SubId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)

In your case, substitute 'Code' for SubId. Then you can create this type in T-SQL, populate it with codes and pass it as a parameter to your stored proc.

Your stored proc would be set up to take this parameter as follows:

CREATE PROCEDURE [dbo].[SomeStoredProc]
    @ModuleList ModuleList READONLY
AS
...

As then you can treat @ModuleList just like a table. In your case, a table of codes.

Upvotes: 3

Related Questions