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;

Answer 1

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;

Answer 2

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
 ..........