Blog

In most cases stored procedures are developed so that it accepts a number of input parameters with a single value. But a common requirement is to be able to submit multi value parameters. That is, parameters that accept several values as opposed to a single value.

In SQL Server 2008, table values parameters in stored procedures was introduced.

Before SQL Server 2008 the most commonly used methods to create multi valued parameters in stored procedures were either to use xml that then is shredded, or to use a delimited list that is split either by T-SQL or by CLR code into multiple values. See this article by Aaron Bertrand for a comparison of the different methods available.

Those solutions worked well most of the time, but with the release of SQL Server 2008 Table Valued Parameters (TVP) as input to stored procedures became available. This method is a lot easier to work with, and in almost all cases outperforms previous methods.

Note however, that a table passed as a parameter need to be READONLY. So you can’t modify the content in a TVP in any way. You’d have to save the table to an ordinary temp table or table variable to do that.

Let’s say you have a table with employees, and what department they belong to (just ignore normalization rules in this demonstration please 🙂 ):

CREATE DATABASE TabVal
GO

USE TabVal
GO

CREATE TABLE dbo.Employees
	(
		EmployeeID INT IDENTITY(1,1) NOT NULL CONSTRAINT EmployeeID_PK PRIMARY KEY CLUSTERED
		,EmployeeName VARCHAR(100) NOT NULL
		,Department VARCHAR(100)
	)
GO

INSERT
	dbo.Employees (EmployeeName, Department)
VALUES
	('Tomas Lind', 'IT')
	,('Sue', 'Accounting')
	,('John', 'IT')
	,('Adam', 'Accounting')
	,('Hugo', 'Sales')
GO

Now if we want to create a procedure that accepts a list of departments as input and returns the matching employees, a table valued parameter can be used in that stored procedure. First, we need to create a new table data type:

CREATE TYPE DepartmentTableType AS TABLE
	(
		Department VARCHAR(100)
	)
GO

This new type is then used when we declare the input parameters in the stored procedure. The table can be accessed as any table within the T-SQL statements in the procedure, as the example demonstrates:

CREATE PROCEDURE dbo.GetEmployeesInDepartments
	(
		@tbl_Department DepartmentTableType READONLY
	)

AS

SET NOCOUNT ON

SELECT
	T0.EmployeeID
	,T0.EmployeeName
	,T0.Department
FROM
	dbo.Employees AS T0
JOIN
	@tbl_Department AS T1
ON
	T0.Department = T1.Department
GO

So, to use this stored procedure, we need to create a table (of the defined type above) that holds the list of departments, and then pass this table to the stored procedure:

DECLARE @tbl_Demo AS DepartmentTableType

INSERT @tbl_Demo VALUES ('Accounting'), ('Sales')

That’s it! Now we can use the stored procedure to retrieve all employees in the departments Accounting and Sales for instance. Just add the excecution of the procedure (last row):

DECLARE @tbl_Demo AS DepartmentTableType
INSERT @tbl_Demo VALUES ('Accounting'), ('Sales')
EXEC dbo.GetEmployeesInDepartments @tbl_Demo

For more information, see Arrays and Lists in SQL Server 2008 by Erland Sommarskog.