Table variables in SQL Server 2008

Microsoft SQL Server 2008 introduces another important change to table variables. The latest SQL Server edition allows you to create a table variable as an input or output parameter of a stored procedure. An array of information can now be passed to a stored procedure without creating a string or XML block and then having to parse it out after executing the stored procedure. There are some restrictions on this however. The table variable parameter must be declared based on a table data type, and the variable must be read-only. When compared to not having this ability at all, these restrictions are minor inconveniences at best.

In the below sample code, a user-defined table type is created and a stored procedure is then defined using this table type. A local variable is declared also using the table type, data is loaded into the variable and then the array of data is passed from the local variable to the input parameter where the records are used within the stored procedure. Here is an example:


create type tt_example AS TABLE
(spid int)
go
create procedure usp_example
@spids tt_example READONLY
AS
SELECT *
FROM @spids
GO
declare @spids tt_example

insert into @spids
select top 10 spid
from sys.sysprocesses

exec usp_example @spids=@spids