What's new in Sql Server 2008 (A string of articles by Irfan AQ)

Table Value Parameters

Another exciting feature of Sql Server 2008 is passing a parameter of table type to a stored procedure or function. Note that it’s parameter of table type, which means that first you have to create the table type and then pass it to the Sproc or function.

Below is a simple example which will create a table type for dventureWorks.HumanResources.Department, and we will create a Sproc which accepts only one parameter of table type and then execute it by passing a variable of new table type:


/* Creating reusable table type */

CREATE TYPE DepartmentType AS TABLE
(
Name VARCHAR (50) NOT NULL,
GroupName VARCHAR (50) NOT NULL
)
GO

/* Creating s Sproc to accept table valued parameter. This will print all the data in the parameter table, but many other operations can also be performed. However, do note that the parameter table cannot be modified */

CREATE PROCEDURE usp_TestTableType
@TableType DepartmentType READONLY
AS
BEGIN
SELECT * FROM @TableType;
END
GO

/* Creating a table variable and inserting four records in it */

DECLARE @TableVariable AS DepartmentType
INSERT INTO @TableVariable (Name, GroupName)
VALUES
('Dept1', 'Group ABC'),
('Dept2', 'Group ABC'),
('Dept3', 'Group XYZ'),
('Dept4', 'Group ABC')

/* Executing stored procedure */

EXEC usp_TestTableType @TableVariable

/* Droping the newly created objects */

DROP PROC usp_TestTableType
DROP TYPE departmenttype


Note that in this Sproc, there are some limitations; first we cannot modify the data in the parameter table henceforth it has to be created as READONLY every time. Secondly, table valued parameters cannot be of output type. Also, these table valued parameters are destroyed as soon as the flow goes out of current scope, therefore, all the insertion or updates on table-valued parameters have to be made in a single batch before passing it to the stored procedure or function.
A good thing is that these table types parameters are not handled in memory. Rather, these are created (materialized) in TEMPDB. This enables us to pass large amount of data as table valued parameters.