Using table valued parameters to efficiently pass lists into SQL Server stored procedures

While optimizing some code, I came across this very comprehensive article on the most efficient way to pass a list of items into a SQL Server stored procedure. To summarize the article the fastest way to pass in a list is to use table valued parameters, but the ORM tool that we are currently using Mindscape Lightspeed has no support for table valued parameters. In their defense though, I’ve yet to see any ORM tool that has support for table valued parameters. Which is weird since these have been around since SQL Server 2008, which is plenty of time to have added them in by now. This is how I built my own calling mechanism for table valued parameters.

To begin with when using this technique you’ll need to create the table valued parameter type. In this example I’m just passing in a set of id’s to do a query off of.

You then need to create the stored procedure with the table valued parameter defined. NOTE: It’s important to pass it in using READONLY, otherwise it won’t pass.

Now that we have the stored procedure setup, we need to build the code that populates the table valued parameter. The following code will take a comma delimited string and construct a list of SqlDataRecords to be passed into the table valued parameter.

Now the following code will call our BuildIdRecordsFromCSV function to package all of the SqlDataRecords into a structured parameter, which is what ADO.Net calls a table valued parameter.

Now all we have to do is have a calling function that puts these pieces together and calls the stored procedure.

Obviously this takes a little bit longer than just calling it with a xml parameters or some other method of passing in a list of records, mainly due to ORM’s not supporting table valued parameters. But it is more than worth it in performance if your passing in a large number of records.

Mike GriffithUsing table valued parameters to efficiently pass lists into SQL Server stored procedures