In previous versions of SQL Server, it was not
possible to share the contents of table variables between stored
procedures. SQL Server 2008 changes that with the introduction of
table-valued parameters, which allow you to pass table variables to
stored procedures as input parameters. Table-valued parameters provide
more flexibility and, in many cases, better performance than temporary
tables as a means to pass result sets between stored procedures.
To create and use table-valued parameters, you must first create a user-defined table type as a TABLE data type and define the table structure. This is done using the CREATE TYPE command, as shown in Listing 1.
Listing 1. Defining a User-Defined Table Type
if exists (select * from sys.systypes t where t.name = 'ytdsales_tabletype'
and t.uid = USER_ID('dbo'))
drop type ytdsales_tabletype
go
CREATE TYPE ytdsales_tabletype AS TABLE
(title_id char(6),
title varchar(50),
pubdate date,
ytd_sales int)
go
|
After creating the
user-defined table data type, you can use it for declaring local table
variables and for stored procedure parameters. To use the table-valued
parameter in a procedure, you create a procedure to receive and access
data through a table-valued parameter, as shown in Listing 2.
Listing 2. Defining a Stored Procedure with a Table-Valued Parameter
/* Create a procedure to receive data for the table-valued parameter. */
if OBJECT_ID('tab_parm_test') is not null
drop proc tab_parm_test
go
create proc tab_parm_test
@pubdate datetime = null,
@sales_minimum int = 0,
@ytd_sales_tab ytdsales_tabletype READONLY
as
set nocount on
if @pubdate is null
-- if no date is specified, set date to last year
set @pubdate = dateadd(month, -12, getdate())
select * from @ytd_sales_tab
where pubdate > @pubdate
and ytd_sales >= @sales_minimum
return
go
|
Then, when calling that
stored procedure, you declare a local table variable using the table
data type defined previously, populate the table variable with data, and
then pass the table variable to the stored procedure (see Listing 3).
Listing 3. Executing a Stored Procedure with a Table-Valued Parameter
/* Declare a variable that references the table type. */
declare @ytd_sales_tab ytdsales_tabletype
/* Add data to the table variable. */
insert @ytd_sales_tab
select title_id, convert(varchar(50), title), pubdate, ytd_sales
from titles
/* Pass the table variable populated with data to a stored procedure. */
exec tab_parm_test '6/1/2001', 10000, @ytd_sales_tab
go
title_id title ytd_sales
-------- -------------------------------------------------- -----------
BU2075 You Can Combat Computer Stress! 18722
MC3021 The Gourmet Microwave 22246
TC4203 Fifty Years in Buckingham Palace Kitchens 15096
|
The scope of a table-valued
parameter is limited to only the stored procedure to which it is passed.
To access the contents of a table-valued parameter in a procedure
called by another procedure that contains a table-valued parameter, you
need to pass the table-valued parameter to the subprocedure. Listing 4 provides an example of a subprocedure and alters the procedure created in Listing 4 in this article to call the subprocedure.
Listing 4. Passing a Table-Valued Parameter to a Subprocedure
/* Create the sub-procedure */
create proc tab_parm_subproc
@pubdate datetime = null,
@sales_minimum int = 0,
@ytd_sales_tab ytdsales_tabletype READONLY
as
select * from @ytd_sales_tab
where ytd_sales <= @sales_minimum
and ytd_sales <> 0
go
/* modify the tab_part_test proc to call the sub-procedure */
alter proc tab_parm_test
@pubdate datetime = null,
@sales_minimum int = 0,
@ytd_sales_tab ytdsales_tabletype READONLY
as
set nocount on
if @pubdate is null
-- if no date is specified, set date to last year
set @pubdate = dateadd(month, -12, getdate())
select * from @ytd_sales_tab
where pubdate > @pubdate
and ytd_sales >= @sales_minimum
exec tab_parm_subproc @pubdate,
@sales_minimum,
@ytd_sales_tab
return
go
/* Declare a variable that references the type. */
declare @ytd_sales_tab ytdsales_tabletype
/* Add data to the table variable. */
insert @ytd_sales_tab
select title_id, convert(varchar(50), title), pubdate, ytd_sales
from titles
where type = 'business'
/* Pass the table variable populated with data to a stored procedure. */
exec tab_parm_test '6/1/2001', 10000, @ytd_sales_tab
go
title_id title pubdate ytd_sales
-------- -------------------------------------------------- ---------- -----------
BU2075 You Can Combat Computer Stress! 2004-06-30 18722
title_id title pubdate ytd_sales
-------- -------------------------------------------------- ---------- -----------
BU1032 The Busy Executive's Database Guide 2004-06-12 4095
BU1111 Cooking with Computers: Surreptitious Balance Shee 2004-06-09 3876
BU7832 Straight Talk About Computers 2004-06-22 4095
|
Table-Valued Parameters Versus Temporary Tables
Table-valued
parameters offer more flexibility and in some cases better performance
than temporary tables or other ways to pass a list of values to a stored
procedure. One benefit is table-valued parameters do not acquire locks
for the initial population of data from a client. Also, table-valued
parameters are memory resident and do not incur physical I/O unless they
grow too large to remain in cache memory.
However, table-valued parameters do have some restrictions:
SQL Server does not create or maintain statistics on columns of table-valued parameters.
Table-valued parameters can be passed only as READONLY input parameters to T-SQL routines. You cannot perform UPDATE, DELETE, or INSERT operations on a table-valued parameter within the body of the stored procedure to which it is passed.
Like table variables, a table-valued parameter cannot be specified as the target of a SELECT INTO or INSERT EXEC statement. They can only be populated using an INSERT statement.
