Using the table DatatypeSQL 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:
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:
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.
|