Using Table-Valued Parameters in SQL Server Stored Procedures

When you need to pass a definite number of parameters to a stored procedure it’s quite simple. You should define as much parameters as needed. But when you need to pass a non-definite number of parameters (maybe 5, 7, 10 or even 100) it’s harder to implement.

There are some ways to make it:

  1. convert values to string, for example, separated with comma;
  2. pass as XML;
  3. use a table-valued parameter.
In the first two cases you must serialize and deserialize values. The last methods allows to use a parameter like a standard database table. I’ll use it to implement the stored procedure that save phone numbers of the client.
  • create a table type for Phone Numbers

  • create a stored procedure

Important! We must declare a table-value parameter as READONLY.

Now we can declare a variable with type PhoneNumberType, insert some rows into it and call the stored procedure. I use this approach for unit test.
SaveClientPhoneNumbers unit test
[UPDATE 2016-11-18]
There are some considerations about performance of table-valued parameters. Look at Jeremiah Peschka’s post to get more information Using SQL Server’s Table Valued Parameters

Leave a Reply

Your email address will not be published. Required fields are marked *