Using Table Valued Parameters in a stored procedure

Question

I am not sure how to change the normal stored procedure with Table Value Parameter.Do I have to create a separate Table or do I have to create a datatype. Any help is greatly appreciated.

ALTER PROCEDURE [dbo].[uspInsertorUpdateINF]
@dp_id                char(32),
@dv_id                char(32),
@em_number            char(12),
@email                varchar(50),
@emergency_relation   char(32),
@option1              char(16),
@status               char(20),
@em_id                char(35),
@em_title             varchar(64),
@date_hired           datetime
AS
BEGIN
SET NOCOUNT ON;

MERGE [dbo].[em] AS [Targ]
USING (VALUES (@dp_id, @dv_id , @em_number, @email, @emergency_relation, @option1, @status, @em_id, @em_title, @date_hired))
AS [Sourc] (dp_id, dv_id, em_number, email, emergency_relation, option1, status, em_id, em_title, date_hired)  
  ON [Targ].em_id = [Sourc].em_id

 WHEN MATCHED THEN
  UPDATE 
     SET dp_id                 = [Sourc].dp_id,
         dv_id                 = [Sourc].dv_id,
         em_number             = [Sourc].em_number,
         email                 = [Sourc].email,
         emergency_relation    = [Sourc].emergency_relation,
         option1               = [Sourc].option1,
         status                = [Sourc].status,
         em_title              = [Sourc].em_title,
         date_hired            = [Sourc].date_hired

  WHEN NOT MATCHED BY TARGET THEN
  INSERT (dp_id, dv_id, em_number, email, emergency_relation, option1, status, em_id, em_title,date_hired)
  VALUES ([Sourc].dp_id, [Sourc].dv_id, [Sourc].em_number, [Sourc].email, [Sourc].emergency_relation, [Sourc].option1, [Sourc].status, [Sourc].em_id, [Sourc].em_title, [Sourc].date_hired);

  END;

If you are asking how to replace all of the arguments passed to the stored procedure to a single table valued argument then the answer is create a table value user defined type and pass it to the stored procedure (don't forget to pass it as readonly) — Zohar Peled
– Zohar Peled, Commented Apr 15, 2015 at 17:12
You would create a user defined table datatype that contains the columns you want. You also might want to take a look at this article and reconsider using MERGE for an upsert. mssqltips.com/sqlservertip/3074/… — Sean Lange
– Sean Lange, Commented Apr 15, 2015 at 17:18

Giorgi Nakeuri · Accepted Answer · 2015-04-15 17:22:11Z

3

First create user defined table type:

CREATE TYPE MyTableType AS TABLE 
( dp_id                char(32),
dv_id                char(32),
em_number            char(12),
email                varchar(50),
emergency_relation   char(32),
option1              char(16),
status               char(20),
em_id                char(35),
em_title             varchar(64),
date_hired           datetime);
GO

Then change your procedure as:

ALTER PROCEDURE [dbo].[uspInsertorUpdateINF]
@customTable MyTableType READONLY
AS
BEGIN
SET NOCOUNT ON;

MERGE [dbo].[em] AS [Targ]
USING @customTable as [Sourc]  
  ON [Targ].em_id = [Sourc].em_id

 WHEN MATCHED THEN
 ..........

answered Apr 15, 2015 at 17:22

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

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

1 Comment

xyz Over a year ago

May be I am asking a silly question. Does the data type creation can be in with the stored procedure

M.Ali · Accepted Answer · 2015-04-15 17:28:36Z

2

I would re-write the whole procedure as follows. Instead of using Merge Statement I would write Two separate (insert and Update) statement and wrap them into ONE Transaction.

TYPE

CREATE TYPE em_TT AS TABLE 
(
dp_id                char(32),
dv_id                char(32),
em_number            char(12),
email                varchar(50),
emergency_relation   char(32),
option1              char(16),
[status]             char(20),
em_id                char(35),
em_title             varchar(64),
date_hired           datetime
)
GO

Stored Procedure

ALTER PROCEDURE [dbo].[uspInsertorUpdateINF]
@em_TT AS em_TT READONLY
AS
BEGIN
SET NOCOUNT ON;

BEGIN TRANSACTION;

  UPDATE e
     SET e.dp_id                 = t.dp_id,
         e.dv_id                 = t.dv_id,
         e.em_number             = t.em_number,
         e.email                 = t.email,
         e.emergency_relation    = t.emergency_relation,
         e.option1               = t.option1,
         e.status                = t.status,
         e.em_title              = t.em_title,
         e.date_hired            = t.date_hired
    FROM [dbo].[em] e 
    INNER JOIN @em_TT t ON e.em_id = t.em_id



  INSERT INTO [dbo].[em](dp_id, dv_id, em_number, email, emergency_relation
                                    , option1, status, em_id, em_title,date_hired)
  SELECT t.dp_id, t.dv_id, t.em_number, t.email
        , t.emergency_relation, t.option1, t.status
        , t.em_id, t.em_title, t.date_hired
  FROM @em_TT t 
  WHERE NOT EXISTS (SELECT 1 
                    FROM [dbo].[em]
                    WHERE em_id = t.em_id)
COMMIT TRANSACTION;

END;

answered Apr 15, 2015 at 17:28

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

3 Comments

xyz Over a year ago

what is e in the >>Update e<< statement

M.Ali Over a year ago

e is the Alias for table [dbo].[em]

M.Ali Over a year ago

@xyz Yes avoid using Merge statement , there is a long list of active bugs with Merger statement have a look at this article Use Caution with SQL Server's MERGE Statement

Collectives™ on Stack Overflow

Using Table Valued Parameters in a stored procedure

2 Answers 2

1 Comment

TYPE

Stored Procedure

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

TYPE

Stored Procedure

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related