Using table value parameter in stored procedure in sql server

Table Valued Parameter In SQL SERVER and .NET (C#):

In SQL Server 2005 and earlier, it is not possible to pass a table variable as a parameter to a stored procedure. When multiple rows of data to SQL Server need to send multiple rows of data to SQL Server, developers either had to send one row at a time or come up with other workarounds to meet requirements. While a VB.Net developer recently informed me that there is a SQLBulkCopy object available in .Net to send multiple rows of data to SQL Server at once, the data still can not be passed to a stored proc.

Possibly the most anticipated T-SQL feature of SQL Server 2008 is the new Table-Valued Parameters. This is the ability to easily pass a table to a stored procedure from T-SQL code or from an application as a parameter.
Table-valued parameters are a new parameter type in SQL Server 2008. It allows you to pass read-only table variables into a stored procedure. Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

We can apply a common example to bulk insert data using one round trip.

User-Defined Table Type

When first taking a look at the new table-valued parameters, I thought that using this feature is a bit complicated. There are several steps involved. The first thing to do is to define a table type. If you look at the Types section under Programmability in the 2008 Management Studio, you will see the new “User-Defined Table Types” (Image 1).



Creating a table

CREATE TABLE [dbo].[tblUsingTableValuedParameter](

[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Address] [nvarchar](100) NULL,
CONSTRAINT [PK_tblUsingTableValuedParameter] PRIMARY KEY CLUSTERED
([ID] ASC
)) ON [PRIMARY]
Creating a table value parameter

CREATE TYPE [dbo].[tvp_tblUsingTableValuedParameter] AS TABLE(
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Address] [nvarchar](100) NULL
)

Using Table Value Parameter in T-SQL

The following is an quick example that declares an instance of the table type an populates it with some values. Later we will use C# to create a data table that will populate an instance of a table type.

Declare instance of tvp_tblUsingTableValuedParameter Table Type

DECLARE @tvp tvp_tblUsingTableValuedParameter
-- Add some sample values into our instance of Table Type.
INSERT INTO @tvp (ID, Name, Address)
VALUES
(1, 'Harish Bagdwal', 'Almora Uttarakhand'),
(2, 'Pankaj Paneru', 'Haldwani Uttarakhand'),
(3, 'Pankaj Upadhyay', 'Ramnagar Uttarakhand'),
(4, 'Dilip Rawat', 'Gurgaon Haryana'),
(5, 'Kamal Kant Joshi', 'Shrinagar Uttarakhand'),
(6, 'Narendra Mehta', 'Almora Uttarakhand') 

Show values that exist in table type instance.

SELECT * FROM @tvp

Using table value parameter in stored procedures

In order to use table value parameters in a stored procedure you have to declare an instance of the table type and specify it as read-only. It is mandatory for the table value parameter to be read-only so you cannot modify the data inside of the table type variable inside the stored procedure.

CREATE PROCEDURE [dbo].[uspInserttblUsingTableValuedParameter]
@tvp tvp_tblUsingTableValuedParameter READONLY
AS
BEGIN
INSERT INTO dbo.tblUsingTableValuedParameter (ID, Name, Address)
SELECT ID, Name, Address
FROM @tvp
END

Calling Table Valued Parameter from a .Net Application. How can we use this table value parameter in C Sharp dot net C# ?

Table Value Parameters in .NET (C#)

The following code below generates a data table in C#. A data table is a common data type used to simulate a table. This data table will be used as our table value parameter and will be used by the stored procedure created above. The data table is not the only type that can be used for table value parameters in C#. The DataReader and list types are also acceptable.

DataTable dtTvp; // create data table to insert items

dtTvp= new DataTable("Items");

dtTvp.Columns.Add("ID",typeof(string));

dtTvp.Columns.Add("Name", typeof(string));

dtTvp.Columns.Add("Address", typeof(string));

dtTvp.Rows.Add(1, "Name1", "Address1"); 

dtTvp.Rows.Add(2, "Name1", "Address1"); 

dtTvp.Rows.Add(3, "Name1", "Address1"); 

dtTvp.Rows.Add(4, "Name1", "Address1"); 

dtTvp.Rows.Add(5, "Name1", "Address1"); 

Creating sql connection to pass table value parameter

SqlConnection con; // modify connection string to connect to your database
string conStr = "Server=localhost;Database=MSSQLTIPS;Trusted_Connection=True;";
con = new SqlConnection(conStr);
con.Open();
using (con)
{
// Configure the SqlCommand and SqlParameter.
SqlCommand sqlCmd = new SqlCommand("dbo.uspInserttblUsingTableValuedParameter", con);
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@tvp", dtTvp); //Needed TVP tvpParam.SqlDbType = SqlDbType.Structured; //tells ADO.NET we are passing TVP sqlCmd.ExecuteNonQuery();
}
con.Close();

Conclusion

The table-valued parameter feature introduced with SQL Server 2008 has been sorely needed. It will allow developers to write much better performing applications by decreasing round-trips to the server and by letting SQL Server work how it does best – on sets of data. It is not difficult to use once all the pieces are put into place.

Few Example:

SQL Server (2008) Passing Table Parameter to Stored Procedure

1. Define Custom Data Type Matches the table structure

Let's name this custom data type NewMemberTable. We declare it like this.

CREATE TYPE NewMemberTable AS TABLE
(
UsrName nvarchar(50) PRIMARY KEY
, FullName nvarchar(50) NOT NULL
, EmailAddy nvarchar(254) NOT NULL
);

Execute that command and you will get a new data type named NewMemberTable.

2. Create Stored Procedure Accepting Table-valued Parameter

CREATE PROCEDURE [dbo].[ImportMembers]

@NewMembers NewMemberTable READONLY

AS

BEGIN

DECLARE @UsrName nvarchar(50);
DECLARE @FullName nvarchar(50);
DECLARE @EmailAddy nvarchar(50);
DECLARE @MemberID int;
DECLARE @EmailID int;

--Declaring cursor for each row in table value parameter

DECLARE NewMembersCursor CURSOR FAST_FORWARD
FOR SELECT UsrName, FullName, EmailAddy FROM @NewMembers;

OPEN NewMembersCursor;
FETCH NEXT FROM NewMembersCursor
INTO @UsrName, @FullName, @EmailAddy

WHILE @@FETCH_STATUS=0
BEGIN

INSERT INTO dbo.Members(UsrName, FullName) VALUES(@UsrName, @FullName); 

SELECT @MemberID=SCOPE_IDENTITY();

SET @EmailID=(SELECT [ID] FROM dbo.EmailAddresses WHERE [Address]=@EmailAddy);

IF @EmailID IS NULL
BEGIN

INSERT INTO dbo.EmailAddresses([Address], RefCount) VALUES(@EmailAddy, 0);

SELECT @EmailID=SCOPE_IDENTITY();
END;

UPDATE dbo.EmailAddresses SET RefCount=RefCount+1 WHERE [ID]=@EmailID;

INSERT INTO dbo.MemberEmailAddresses(Member, EmailAddy, Active) VALUES(@MemberID, @EmailID, 1);

FETCH NEXT FROM NewMembersCursor
INTO @UsrName, @FullName, @EmailAddy
END;

CLOSE NewMembersCursor;
DEALLOCATE NewMembersCursor;
END;
___________________________________________________________________________
Example-

CREATE TYPE [dbo].[tvpProductMaster] AS TABLE(
[Id] [int] NOT NULL,
[ProductVariantName] [nvarchar](150) NULL,
[ProductVariantId] [nvarchar](150) NULL,
[ProductName] [nvarchar](150) NULL,
[ProductId] [nvarchar](150) NULL,
[active] [bit] NULL,
[createdon] [nvarchar](50) NULL,
[createdby] [nvarchar](50) NULL,
[updatedon] [nvarchar](50) NULL,
[updatedby] [nvarchar](50) NULL
)
GO

DECLARE @tvpParam AS tvpProductMaster
INSERT @tvpParam  
SELECT 253,  
  N'255/55 R 17 104V IIII' ,  
  N'13b07409-cd6a-e211-a3f1-d4ae528ba060',  
  N'LATITUDE TOUR HP',  
  N'f471a4b9-c96a-e211-a3f1-d4ae528ba060',  
  0,  
   '03/20/2014',  
   'Yogesh',  
   '03/20/2014',  
   'Yogesh'
   exec SP_InsertUpdateProductMaster @tvpParam
  set identity_insert ProductMaster on
  select * from @tvpParam
  select * from ProductMaster

__________________________________________________________________________
Example 2

SQL Server (2008) Passing Table Parameter to Stored Procedure

1. Define Custom Data Type Matches the table structure

Let's name this custom data type NewMemberTable. We declare it like this.


CREATE TYPE NewMemberTable AS TABLE
(
UsrName nvarchar(50) PRIMARY KEY
, FullName nvarchar(50) NOT NULL
, EmailAddy nvarchar(254) NOT NULL
);

Execute that command and you will get a new data type named NewMemberTable.

2. Create Stored Procedure Accepting Table-valued Parameter

CREATE PROCEDURE [dbo].[ImportMembers]
@NewMembers NewMemberTable READONLY
AS
BEGIN
DECLARE @UsrName nvarchar(50);
DECLARE @FullName nvarchar(50);
DECLARE @EmailAddy nvarchar(50);
DECLARE @MemberID int;
DECLARE @EmailID int;

DECLARE NewMembersCursor CURSOR FAST_FORWARD
FOR SELECT UsrName, FullName, EmailAddy FROM @NewMembers;

OPEN NewMembersCursor;
FETCH NEXT FROM NewMembersCursor
INTO @UsrName, @FullName, @EmailAddy

WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO dbo.Members(UsrName, FullName)
VALUES(@UsrName, @FullName);
SELECT @MemberID=SCOPE_IDENTITY();

SET @EmailID=(SELECT [ID] FROM dbo.EmailAddresses WHERE [Address]=@EmailAddy);

IF @EmailID IS NULL
BEGIN
INSERT INTO dbo.EmailAddresses([Address], RefCount)
VALUES(@EmailAddy, 0);
SELECT @EmailID=SCOPE_IDENTITY();
END;

UPDATE dbo.EmailAddresses SET RefCount=RefCount+1 WHERE [ID]=@EmailID;

INSERT INTO dbo.MemberEmailAddresses(Member, EmailAddy, Active)
VALUES(@MemberID, @EmailID, 1);

FETCH NEXT FROM NewMembersCursor
INTO @UsrName, @FullName, @EmailAddy
END;

CLOSE NewMembersCursor;
DEALLOCATE NewMembersCursor;
END;