Home > DeveloperSection > Articles > Passing table as a parameter to stored procedure

Passing table as a parameter to stored procedure


Database Database 
Ratings:
1 Comment(s)
 2970  View(s)
Rate this:

Passing table as a parameter to stored procedure

 

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.


Passing table as a parameter to stored procedure

By Kamlakar Singh on   2 years ago
nice article.

Don't want to miss updates? Please click the below button!

Follow MindStick