SQL Server 2000 introduces a new datatype?the table datatype. Table variables are defined similarly to regular tables except they are defined in a DECLARE statement, rather than using CREATE TABLE:
DECLARE @table_variable TABLE ({ column_definition | table_constraint }
[ ,...n ])
The following is a simple example showing the use of a table variable in a stored procedure:
-- proc to get year-to-date sales for all books published since specified date -- with ytd_sales greater than specified threshold create proc tab_var_test @pubdate datetime = null, @sales_minimum int = 0 as declare @ytd_sales_tab TABLE (title_id char(6), title varchar(80), ytd_sales int) if @pubdate is null -- if no date is specified, set date to last year set @pubdate = dateadd(month, -12, getdate()) insert @ytd_sales_tab select title_id, title, ytd_sales from titles where pubdate > @pubdate and ytd_sales > @sales_minimum select * from @ytd_sales_tab return go exec tab_var_test '6/1/1991', 10000 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
Table variables can be used in functions, stored procedures, and batches. Consider using table variables instead of temporary tables whenever possible because they provide the following benefits:
Table variables are memory resident and require no space in tempdb.
When table variables are used in stored procedures, fewer recompilations of the stored procedures occur than when temporary tables are used.
Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.
A table variable behaves like a local variable, and its scope is limited to the stored procedure in which it is declared. It is cleaned up automatically at the end of the function, stored procedure, or batch in which it is defined.
A table variable can be used like a regular table in SELECT, INSERT, UPDATE, and DELETE statements. However, a table variable cannot be used in the following statements:
INSERT INTO table_variable EXEC stored_procedure
SELECT select_list INTO table_variable ...
You need to keep a couple of other limitations in mind when considering using table variables in stored procedures. First, table variables cannot be used as stored procedure parameters. You cannot pass a table variable as an input or output parameter for a stored procedure, nor can you access a table variable declared outside the currently executing stored procedure. If you need to share resultsets between stored procedures, you have to use temporary tables. Second, you cannot create indexes on table variables using the CREATE INDEX command. You can, however, define a primary or unique key on the table variable when it is declared.
TIPOne solution to the inability of stored procedures to pass table variables as output parameters is to convert the stored procedure to a user-defined function if possible. User-defined functions can return a table resultset that can be referenced in a SELECT statement just like a regular table. Thus, you can include it in an insert ... select ... statement and insert the results into a local variable (something you cannot do with a resultset from a stored procedure). For example, I will take the previous tab_var_test stored procedure and convert it to a user-defined function.
You cannot use the getdate() function inside a user-defined function, so it has to be removed to convert the stored procedure to a function. For more information on defining and using user-defined functions, see Chapter 30, "User-Defined Functions." |