Passing Table as a Parameter to Stored Procedure

You may have faced a situation where you need to pass a bunch of rows & columns as a parameter to your Stored Procedure. To overcome from that situation you can pass as many rows and columns to your procedure.

Here in my blog, I’m going to explain to you guys a very simple way to perform this.

First of all, you need a Table

CREATE TABLE MyRecords

(

ID INT PRIMARY KEY IDENTITY(1,1),

FirstName VARCHAR(50),

LastName VARCHAR(50),

DOB DATETIME

)

Next, you need to create a UDT (User Defined DataType)

CREATE TYPE MyType AS TABLE

(

FirstName VARCHAR(50),

LastName VARCHAR(50),

DOB DATETIME

)

Now, let’s create a Procedure where we will pass a table as a parameter.

CREATE PROCEDURE SP_InsertRecords

(

@table MyType READONLY

)

BEGIN

INSERT INTO MyRecords(FirstName,LastName,DOB)

SELECT FirstName,LastName,DOB FROM @table

END

Now, it’s time to write the C# Code.

DataTable dt = new DataTable ();// Please add some rows to DataTable first

SqlConnection con = new SqlConnection(/*Pass Connection string here*/);

con.Open();

SqlCommand cmd = new SqlCommand(“SP_InsertRecords”,con);

cmd.Parameters.Add(“@table”, SqlDbType.Structured);

cmd.Parameters["@table"].Value = dt;

cmd.ExecuteNonQuery();

Now, run your application and pass as many rows as you want at once.

I hope it will help some of you guys.