In this article, I’m going to explain how to pass a table as a parameter to stored procedure in sql server.

First create 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 parameter. 

CREATE PROCEDURE SP_InsertRecords
(
@table MyType READONLY
)
AS
   BEGIN
       INSERT INTO MyRecords(FirstName,LastName,DOB)
       SELECT FirstName,LastName,DOB FROM @table
  END

 Now it’s time to write 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 you want, it’s good to go for this approach if you need to pass hundreds or thousands of rows at once.

  Modified On Nov-30-2017 12:43:55 AM

Leave Comment