#TIL How to Pass a Table of Data Into a Stored Procedure

I already knew how to do this, but it’s what I was working on yesterday so I figured I’d regurgitate it for others who may not know. This technique is especially handy when you find yourself doing a bunch of individual database queries, such as a “Get By ID” call on 5 specific items.

As of SQL Server 2008, you can create what are called “User Defined Table Types”. These are basically table definitions that can be used as variables in your other stored procedures or user-defined functions. To create one, it’s as simple as a type definition:

1
2
3
CREATE TYPE MyTableType AS TABLE
  (Column1 int,
   Column2 VARCHAR(50));

This will create a user-defined table type called MyTableType – you can verify this in SQL Server Management Studio by going to Programmability –> Types –> User-Defined Table Types. Now that we have one created, what can we do with it? First off, you can use it as an input variable to your stored procedure.

1
2
CREATE PROCEDURE MyStoredProcedure ( @MyTable MyTableType ) AS
  -- body of sproc here

Once you have that table passed in, you can treat it as any other table in SQL – run select queries against it, insert, modify, and delete data, etc. As I lead the article with, this is a great way of passing a collection of data into a stored procedure.

If you want to actually execute this stored procedure, there’s a few different things you can do. In SQL, you’d have to declare and populate the table before calling EXEC.

1
2
3
4
5
6
-- create variable based on table type definition
DECLARE @MyTempTable MyTableType
-- insert into variable just like any other table
INSERT INTO @MyTempTable VALUES (1, "One")
-- pass table to stored procedure
EXEC MyStoredProcedure @MyTempTable

To do the same thing through C#, you’ll have to create a DataTable and pass it as a parameter to your SQL Command.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
var dataTable = new DataTable();
//define our columns to match our user-defined table type
dataTable.Columns.Add("Column1", typeof(int));
dataTable.Columns.Add("Column2", typeof(string));
//insert a row
dataTable.Rows.Add(1, "ASDF");

var sqlCommand = new SqlCommand("MyStoredProcedure");
sqlCommand.CommandType = CommandType.StoredProcedure;
//add table as parameter, matching parameter name
var sqlParameter = sqlCommand.Parameters.AddWithValue("@MyTable", 
  dataTable);
//set the DB type as a "structured" object
sqlParameter.SqlDbType = SqlDbType.Structured;
//set the type name so our sql server knows what we're talking about
sqlParameter.TypeName = "MyTableType";

Comments