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

29 בApril 2014

tags: , ,
no comments

image

Using SQL Server 2012

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

image

Create a type which will use for the input parameter:

image

Create a new stored procedure

image

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

image

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:

image

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.

image

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.

image

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>

*