A colleague created a stored procedure that returns some data from a specific table. Nothing special would you think and you are right. The only reason we were using a stored procedure here is that we had a very specific requirement that every attempt to read data from this specific table should be logged.
Here is a simplified version of the stored procedure he created:
What I want to talk about in this post is the usage of a (comma separated) string parameter that is used to construct the filter criteria for the query.
Remark: This version of the stored procedure is already better than the original version that was using dynamic SQL to construct an IN clause:
Using Table-Valued Parameters
We can further improve the procedure above by using a table valued parameter.
To use table-valued parameters instead of comma-separated strings in your stored procedure, you can follow these steps:
Step 1: Create a Table-Valued Parameter Type
First, you need to create a table-valued parameter type. This type will represent the structure of the values you want to pass to your stored procedure.
Step 2: Modify the Stored Procedure
Now we can modify our stored procedure to accept the new table-valued parameter type:
Step 3: Call the Stored Procedure with our Table-Valued Parameter
When calling the stored procedure, you'll need to declare a variable of the table type and populate it with your values:
Key advantages
Using this approach offers some advantages:
- Performance: Table-valued parameters are generally more efficient than using comma-separated strings, especially for larger datasets.
- Allows using strongly typed parameters: We don’t have to cast the parameter values to the correct datatype.
- Supports larger data sets: The size of the string parameter is limited. By using table value parameters we can support larger parameter sets.
More information
Use table-valued parameters (Database Engine) - SQL Server | Microsoft Learn