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 Confundido 
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. Sonrisa 

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:

image

 

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:

image

 

 

 

 

 

 

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

image

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

image

And here is the result:

image

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:

image

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:

image

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:

image

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

image

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

image

And the orders are all inserted:

image

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