---
title: "Passing table as a parameter to stored procedure"  
description: "In this article, I’m going to explain how to pass a table as a parameter to stored procedure in sql server."  
author: "Sanjay Singh"  
published: 2014-03-28  
updated: 2017-11-30  
canonical: https://www.mindstick.com/articles/1389/passing-table-as-a-parameter-to-stored-procedure  
category: "database"  
tags: ["database"]  
reading_time: 1 minute  

---

# 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 firstSqlConnection 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.

---

Original Source: https://www.mindstick.com/articles/1389/passing-table-as-a-parameter-to-stored-procedure

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.