User-Defined Table Type and Table Valued Parameters In Stored Procedure To Reduce The Code Size In Code Behind File

Table Valued Parameter allows a table (i.e multiple rows of data) to be passed as a parameter to a stored procedure.

Introduction

SQL Server now supports table valued parameters which allow us to send data tables as parameters to Stored Procedures. It still uses the same ADO.NET API.

Description

Table Valued Parameter allows a table (i.e multiple rows of data) to be passed as a parameter to a stored procedure from T-SQL code or from an application. It was not possible to pass a table variable as a parameter to a stored procedure in old versions of SQL Server.

To pass multiple rows to a stored procedure using Table Valued Parameter, follow the below steps.

Steps

Create one table named "MyUDTable".

Create Table MyUDTable
(
    Id int primary key,
    Name nvarchar(50),
    Gender nvarchar(10)
)
Go

 Create User-defined TableType named "MyUDTableType".

 CREATE TYPE MyUDTableType AS TABLE
(
    Id INT PRIMARY KEY,
    Name NVARCHAR(50),
    Gender NVARCHAR(10)
)
Go

 

Create one stored procedure named "Sp_InsertMyUDTableByMyUDTableType". 

CREATE PROCEDURE Sp_InsertMyUDTableByMyUDTableType
@MyUDTableType MyUDTableType READONLY
AS
BEGIN
    INSERT INTO MyUDTable
    SELECT * FROM @MyUDTableType
END

Here, the parameter of user-defined TableType (@MyUDTableType) must be passed as read-only to stored procedure.Then, in insert statement, it is not required to mention column names of table in stored procedure; insteadd we pass table valued parameters of user-defined TableType Named "MyUDTableType".

Let's use the user-defined TableType as a parameter in the stored procedure.

Table valued parameters must be passed as read-only to stored procedures, functions etc. This means you are unable to perform DML operations like INSERT, UPDATE or DELETE on a table-valued parameter in the body of a function, stored procedure etc.

Then, run the below SQL

select * from MyUDTable

 

 

Declare a table variable, insert the data and then pass the table variable as a parameter to the stored procedure.

DECLARE @MyUserDTableType MyUDTableType
INSERT INTO @MyUserDTableType VALUES (1, 'Mark', 'Male')
INSERT INTO @MyUserDTableType VALUES (2, 'Mary', 'Female')
INSERT INTO @MyUserDTableType VALUES (3, 'John', 'Male')
INSERT INTO @MyUserDTableType VALUES (4, 'Sara', 'Female')
EXECUTE Sp_InsertMyUDTableByMyUDTableType @MyUserDTableType

In insert statement we used table variable instead of Columns of tabled named "MyUDTable".

Then execute stored procedure to insert records to table named "MyUDTable" by using table variable named "@MyUserDTableType".

Then run the below sql

select * from MyUDTable

 

 

All 4 records are inserted successfully in the tabled named "MyUDTable".

Merits

Code size in C# will be reduced using ony single user-defined TableType parameter instead of many parameters of stored procedure. For example, 

Without User-defined Table Type as a parameter:

To execute this procedure, we can create a data table and add the rows into it. Then, pass this data table as a parameter to the database. 

DataTable dt = new DataTable();
using(SqlConnection conn = new SqlConnection("your connection string"))
   {
       SqlCommand cmd = conn.CreateCommand();
       cmd.CommandType = System.Data.CommandType.StoredProcedure;
       cmd.CommandText = "dbo.Sp_InsertSampleDetails"; //The name of the other stored procedure.
       SqlParameter param = cmd.Parameters.AddWithValue("@param1", dt); //The next 4 related parameters of this other stored procedure.
       SqlParameter param = cmd.Parameters.AddWithValue("@param2", dt);
       SqlParameter param = cmd.Parameters.AddWithValue("@param3", dt);
       SqlParameter param = cmd.Parameters.AddWithValue("@param4", dt);
       conn.Open();
       cmd.ExecuteNonQuery();
       conn.Close();
   }

Here, I put all the parameters with stored procedure with 4 lines of code.

with User-defined TableType as a parameter,

using(SqlConnection conn = new SqlConnection("your connection string"))
    {
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "dbo.Sp_InsertMyUDTableByMyUDTableType"; //The name of the above mentioned stored procedure.
        SqlParameter param = cmd.Parameters.AddWithValue("@MyUDTableType", dt); //Here"@MyUDTableType" is the User-defined Table Type as a parameter.
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    }

Here, I put one parameter with stored procedure by putting a single line of code.

Summary

In this blog, we have learned the following.

1. What is User-Defined Table Type.
2. How to implement it in stored procedure.
3. How to implement it in C# Code-Behind File.
4. Merits of using User-Defined TableType in real time.