SQL Server - Use Table Valued parameters to construct an IN statement

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

	CREATE PROCEDURE [dbo].[READBYKEYS] (
	@list NVARCHAR(MAX),
	@user NVARCHAR(50),
	)
	AS
	BEGIN
	SET NOCOUNT ON;
	//We should log every attempt to query this datasource
	INSERT INTO AUDIT_LOG (@user, @list)
	//Don't do this!
	SET @sql = N'SELECT RR_DATA.ID, NR_RR FROM RR_DATA WHERE id IN( ' + @list +')';
	EXEC sp_executesql @sql;
	END

view raw readybykeys.sql hosted with ❤ by GitHub

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.

	CREATE TYPE IntListType AS TABLE (
	Value INT NOT NULL PRIMARY KEY
	);

view raw CreateType.sql hosted with ❤ by GitHub

Step 2: Modify the Stored Procedure

Now we can modify our stored procedure to accept the new table-valued parameter type:

	CREATE PROCEDURE [dbo].[READBYKEYS] (
	@list IntListType READONLY,
	@user NVARCHAR(50),
	)
	AS
	BEGIN
	SET NOCOUNT ON;
	//We should log every attempt to query this datasource
	INSERT INTO AUDIT_LOG (@user, @list)
	SELECT RR_DATA.ID, NR_RR
	FROM RR_DATA
	WHERE ID IN (SELECT Value FROM @list);
	END

view raw readbykeys.sql hosted with ❤ by GitHub

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:

	DECLARE @list IntListType; INSERT INTO @list (Value) VALUES (1), (2), (3), (4), (5);
	EXEC READBYKEYS @list=@list, @user=‘BaWu’;

view raw CallProcedure.sql hosted with ❤ by GitHub

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