Using the table Datatype

Using the table Datatype

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.

TIP

One 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.

[View full width]

 
 [View full width] 
 -- function to get year to date sales for all books published  since specified  -- date with ytd_sales greater than specified threshold create function tab_function (@pubdate datetime ,                              @sales_minimum int = 0) returns @ytd_sales_tab TABLE (title_id char(6),                               title varchar(80),                               ytd_sales int) as begin insert @ytd_sales_tab     select title_id, title, ytd_sales       from titles       where pubdate > @pubdate         and ytd_sales > @sales_minimum return end go declare @local_tab table (title_id char(6), title varchar(80),  ytd_sales int) insert @local_tab select * from tab_function('6/1/1991', 10000) select * from @local_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 

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."