SQL – Create and use a stored procedure with table as a parameter

29 בApril 2014

tags: , ,
no comments


Using SQL Server 2012

Assuming we have a sample “TestDB” with the following scheme:


Create a type which will use for the input parameter:


Create a new stored procedure


Fill in the stored procedure name, the parameter name and the query


Notice the CostumersIDs is the parameter name and CostumersIdsType is it’s type which we created earlier.

For simplicity, the query I used just returns the received input parameter.

Test the stored procedure in sql server:


What happened here is:

– We created a temporary table of our declared type (CostumersIdsType), and inserted into it a few value.

– Called our stored procedure with the temporary table as a parameter.


Testing the stored procedure

Call the stored procedure from C#

  1. //Call the stored procedure from C#
  2.           conststringstoredProcParamName=“@CostumersIDs”;
  3.           conststringstoredProcName=“sp_MyTestSP”;
  4.           //Prepare the data
  5.           //Build a demo table, containing 3 costumers with different ID’s
  6.           DataTabletable=newDataTable();
  7.           table.TableName=“Customers”;
  8.           table.Columns.Add(“Id”, typeof(int));
  9.           table.Rows.Add(1);
  10.           table.Rows.Add(2);
  11.           table.Rows.Add(3);
  12.           //Prepare the query
  13.           BuildQueryquery=newBuildQuery();
  14.           varspParameters=newDictionary<string, object>(1);
  15.           spParameters.Add(storedProcParamName, table);
  16.           query.ExecuteSP(storedProcName, spParameters);

I won’t expose all the details since it’s out of scope;

Just to mention, I’m using my own class ‘BuildQuery’, which is a simple wrapper over System.Data.SqlClient type: SqlCommand, SqlConnection and SqlDataAdapter. There are nicer ways to do that, check the link at the bottom.

Notice I’m using the same naming for the stored procedure and the parameter.

What happens next is building a DataTable with data, setting it as an input parameter and executing the query.


Results from C# stored procedure call.

More info about stored procedure mapping to entity framework can be found here.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>