How to pass a data table into a stored procedure as a Table Value Parameter (TVP) in SQL server 2008 and above.

In some instances there might be a need to manipulate an entire data set rather than passing individual values as variables into a database. To do this I explain how to start by creating the proc with the parameters then obtaining the inserted identity values from the insert statement to update a related table.

Note the special table inserted which is an SQL table which stores the values of an insert just before this hits your table. Refer MSDN on inserted and deleted Tables

1.      To start with define the table value parameter under your database ->programmability->Types-> User-Defined Table Types

2.      Right click on the User Defined Table Types

Create a data table definition of the set needed to be passed. Clicking on CTRL-Shift-M would bring up the Dialog to create them visually.

The script should look like the following

-- ================================

-- Create User-defined Table Type

-- ================================

USE AdventureWorks

GO

-- Create the data type

CREATE TYPE dbo.MY_TVP AS TABLE

(

       c1 int NOT NULL,

       c2 char(10) NULL,

       c3 datetime NULL,

    PRIMARY KEY (c1)

)

GO

--the table we want the values inserted into note the identity column which we need after the insert is done.

CREATE TABLE [dbo].[tbltest](

       [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

       [c1] [int] NULL,

       [c2] [char](10)  NULL,

       [c3] [datetime]  NULL

      

Once the above is done define your proc and utilize the above TVP as a type as below:

-- =============================================

-- Define a proc with the TVP value

-- =============================================

create PROCEDURE [dbo].[spSave]

       -- Add the parameters for the stored procedure here

       @Mytable MY_TVP Readonly

AS

BEGIN

-- Use this table to hold the inserted Identity values of table 1

Declare @IDList Table(ID int);

       -- this is a general insert, just work with the table variable as per normal table.

             Insert into tbltest (c1,C2,C3)

Output INSERTED.ID Into @IDList(ID)

                    select  * from

                    @Mytable

                     

             insert into relatedTable

                    select ID,@SomeID from @IDList  

      

END