Pass Table As Input Parameter To Stored Procedure In SQL

Hi Guys,

How many of us knows one of the very important new feature of SQL Server 2008 that we can also pass a table type as an input parameter to a SQL stored procedure.

Below is the code sample for this.

Step 1: First Create a Table Employee

CREATE TABLE Employee(
    EmpID        VARCHAR(10)
    , EmpName    VARCHAR(50)
    , Gender     CHAR(1)
    , DOJ        DATETIME 
)
Step 2: Create a Table Type
CREATE TYPE EmpTableType AS TABLE (
    EmpID        VARCHAR(10)
    , EmpName    VARCHAR(50)
    , Gender     CHAR(1)
    , DOJ        DATETIME 
)
Step 3 : Create a Stored Procedure that would take Table Type as parameter that we created in previous step.
CREATE PROCEDURE usp_GetEmpDetils
(
    @EmpDet EmpTableType READONLY 
) 
AS 
BEGIN    
    INSERT INTO Employee
    SELECT * FROM @EmpDet

    SELECT * FROM Employee
END

Step 4 : ADO.Net Code passing DataTable to the TableType Parameter in Stored Procedure from C#
DataTable EmpTable = new DataTable();
EmpTable.Columns.Add("EmpID");
EmpTable.Columns.Add("EmpName");
EmpTable.Columns.Add("Gender");
EmpTable.Columns.Add("DOJ");
DataRow EmpRow = EmpTable.NewRow();
EmpRow["EmpID"] = "EMP0001";
EmpRow["EmpName"] = "Sandeep Mittal";
EmpRow["Gender"] = "M";
EmpRow["DOJ"] = "01/01/2010";        
EmpTable.Rows.Add(EmpRow);
EmpTable.AcceptChanges();SqlConnection connection = new SqlConnection("data source=ServerName;database=DBName;uid=UserID;pwd=Password");
SqlCommand selectCommand = new SqlCommand("usp_GetEmpDetils", connection);
selectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = selectCommand.Parameters.AddWithValue("@EmpDet", EmpTable);
tvpParam.SqlDbType = SqlDbType.Structured;
connection.Open();
grid.DataSource = selectCommand.ExecuteReader();
grid.DataBind();
connection.Close();

That's all. Isn't is very ease and useful for developers??

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s