Practical .NET

Speed Up Stored Procedures with Table Valued Parameters

Peter revisits an earlier tip on how to use stored procedures to speed up your code. This version makes your stored procedures simpler … though you may have to write a little more code to make the call.

• By Peter Vogel
• 06/09/2015

In an earlier column, I suggested that one way to speed up your application was to reduce the trips you make to your database, specifically by avoiding calling a stored procedure multiple times. To enable that, I showed how to pass a stored procedure multiple parameter values in a single call and then, inside the stored procedure, load the parameters into a table where they could be integrated with other SQL statements.

As several readers pointed out, I used very old-fashioned technology to implement this tactic: I concatenated all of the parameters into a string and then, in the stored procedure, broke the string into parts before loading those parts into a table. While my code worked in every version of SQL Server, it was hardly "hip and happening." But, if you're using any version of SQL Server later than SQL Server 2005, you have an alternative: table-valued parameters.

Table-valued parameters let you pass a collection of table rows as a parameter to your stored procedure. The benefit of passing table rows is you can short circuit some of the stored procedure code from my previous column: It's no longer necessary to break up the string (I used a set of T-SQL code I "borrowed" from a client to do that) or insert the values into a table because your parameters are already in a table.

In the examples for this column, I use ADO.NET, but these techniques work equally as well if you're calling your stored procedures from an Entity Framework DbContext object (as I've discussed in a previous column).

Accepting Table Valued Parameters
To use table-valued parameters you first need, in SQL Server Manager, to define a table type to your database. This example defines a table type called JobSpecifications with two columns called JobName and AvailableDate:

CREATE TYPE JobSpecifications AS TABLE 
  (JobName VARCHAR(50), 
  AvailableDate Date );

With the type defined, you can now use it as a parameter in any stored procedure where it makes sense. This example uses the type to accept one or more rows of the JobSpecifications type in a parameter called @JobCriteria:

Create PROCEDURE dbo.GetJobs
  @JobCriteria JobSpecifications ReadOnly
As

In the body of the stored procedure, you can use the parameter like any other table. This example joins the parameter to another table in the actual database to find some matching rows and return the result:

  Select * 
    From JobOpenings jo
      Inner Join @JobCriteria jc
        On  jo.JobName = jc.Name
        And jo.StartDate > jc.AvailableDate
Return

Passing Table-Valued Parameters
But, while everything is better on the T-SQL side of the processing, the code to call the stored procedure can be a little uglier because, under the worst possible scenario, the values you're passing are just sitting around in variables in your application. In that scenario you need to create a DataTable with columns that match your table type and then load it with rows.

For example, code to create a DataTable that matches my JobSpecifications type would look like this:

Dim jobspecs As New DataTable
jobspecs.Columns.Add("JobName", GetType(String))
jobspecs.Columns.Add("AvailableDate", GetType(Date))

This code creates a row from the DataTable, populates the two columns in the row and then adds the row to the table:

Dim rw As DataRow
rw = jobspecs.NewRow()
rw("JobName") = "Administrator"
rw("AvailableDate") = DateTime.Now
jobspecs.Rows.Add(rw)

Finally, of course, I need to pass the DataTable to the stored procedure:

Dim cn As New SqlConnection("...connection string...")
Dim cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = "GetJobs"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("JobCriteria", jobspecs))

If this looks a little intimidating, it is the worst-possible scenario -- many other scenarios are simpler. If, for example, your parameters are in a DataTable in a DataSet, you can just pass the DataTable as your parameter. This example passes a DataTable called Parms in the DataSet held in a variable called ds (this will also work with any method that returns a DataTable, though not with any method that returns some collection of DataRows):

cmd.Parameters.Add(New SqlParameter("JobCriteria", ds.Tables("Parms")))

If you're reading your parameters from a table through a DataReader, then you can pass the DataReader as your parameter. This example uses the AddWithValue method on the Parameters collection to pass a DataReader called rdrParms:

cmd.Parameters.AddWithValue("@JobCriteria", rdrParms)
cmd.Parameters(0).SqlDbType = SqlDbType.Structured

There doesn't, however, seem to be a simple way to pass the results of a LINQ query to a table-valued parameter.

Whichever mechanism you use, once you've got the parameter added you can call the stored procedure and process the results:

Dim rdr As SqlDataReader
cn.Open()
rdr = cmd.ExecuteReader
Do While rdr.Read
  ...do something with the returned rows...
Loop
cn.Close()

While this column has concentrated on one technology for passing multiple parameters to a stored procedure, that's really just a means to an end. The real goal is to make your application run faster by reducing trips to your database. However you do that will make your users say the best thing: "Hey, that was fast!"