How to create and execute a stored procedure using a table as a parameter

 

Some times you need to create a  stored procedure with input parameters for working with rowsets instead of single rows.  For example, let´s say you want to process a bunch of customer orders all at a time. Usually you would write a stored procedure  with input parameters (OrderID, CustomerID, etc.) and would execute the stored procedure multiple times, once for each record you want to  process.

If you need to load 30 records into a table using this sp, you would have to execute it 30 times!. It´s no so good eh  
Fortunatly, SQL Server 2008 lets you use a table as an input parameter so you can fill this table with all the records you want to process and pass this parameter to the sp. You would only need to execute it once which is much better.  

The general steps are:

1. Create a user defined table type (UDTT) .
2. Create a stored procedure with input parameters (one or more) and be sure to use this UDTT for one of the parameters. Declare the parameter as READ ONLY which is required to process the table as an input parameter.
3. When executing the stored procedure, declare a variable of the UDTT type.
4. Fill this variable with some records and proceed to execute the sp passing the variable you just declared.

For this demo, I´m going to build a new table  using the following script:

 

This will be the target table where the info is going to be inserted. I have added a DEFAULT for OrderDate to simplify the demo, this way I will only need to work with 3 fields.

1. Create a UDTT:

 

 

 

 

 

 

2.  Create a stored procedure with an input parameter of OrderDate type:

3. Finally I can execute the stored procedure this way:

And here is the result:

Notice that since all records where inserted at the same time, they all have the same OderDate value. Now I´m going to show how to execute this stored procedure using ADO.NET. It´s very easy, there is a structured data type which can be used to work with table datatypes.

Lets see:

To begin, I have created a new project of type Console application, and I added a new class called Order:

Remember to include System.Data and System.Data.SqlClient namespaces.

When building the parameter for the stored procedure, dont forget to use the structured datatype for the parameter (@OrderList) as shown in the following screen:

When building the method, you can pass a DataTable as an argument in order to use it for the stored procedure parameter. You could also use a List, build a dataset, add a data table and populate it with the list, all inside the method. For simplicity I´m going to use a table and an XML file to load the data inside it.

Here is the complete code for the method inside the Order Class:

And this is the XML file: (OrderList.xml)

Finally, I can use this code to test the method:

And the orders are all inserted:

Now, you can process multiple records at once quick and easy.

Comments

• Anonymous
  October 30, 2010
  Very good and to the point article. Thanks

• Anonymous
  October 31, 2010
  Excellent presentation and useful tip.

• Anonymous
  June 22, 2012
  Great! Thanks

• Anonymous
  July 10, 2012
  Very helpful indeed! thanks!

• Anonymous
  December 06, 2012
  Thanks

• Anonymous
  February 13, 2013
  How does this compare to SQLBulkCopy in speed?

• Anonymous
  July 08, 2013
  top!

• Anonymous
  February 13, 2014
  great job!

• Anonymous
  March 04, 2014
  How can I Fill up Orders table based on values in orderdata table, conditional scenario ?

• Anonymous
  March 05, 2014
  How do you use with an update command