Table variables used in stored procedures or SQL Server

We often encounter this situation. It is often used to create a temporary table with in the stored procedure. This temporary table is used multiple times in this function module. If you use with to create a temporary table with the same function, high performance and high redundancy in Stored ProceduresCodeTo this end, I use table variables to implement this function (not table value variables)

Syntax for defining table variables:

Declare table variable name table (

Whether the column name type is null,

Whether the column name type is null,

Whether the column name type is null,

Whether the column name type is null

)

The following are the stored procedures used in my project:

Proc

 1   Use   [  Xb_quotation_dev2  ] 
 2   Go 
 3  
 4   /*  * *** Object: storedprocedure [DBO]. [usp_updatetodaypricemange] script Date: 02/18/2012 14:34:43 *****  */ 
 5   Set Ansi_nulls On 
 6   Go 
 7  
 8   Set Quoted_identifier On 
9   Go 
 10  
 11   Create   Procedure   [  DBO  ] . [  Usp_updatetodaypricemange  ]  
 12       @ Dt Ty_publictodayprice readonly
 13  As 
 14   Begin 
 15       Set Nocount On ;
 16       Declare   @ Errorsmall   Smallint   =  0 ;
 17       Begin   Tran ;
 18           Set   @ Errorsmall  =-  1 ;
 19           Declare   @ Dt_prices   Table (Areaid Int   Not   Null , Goodsid Int   Not   Null , GuidepriceDecimal ( 18 , 4 ) Not   Null );
 20               With Temp_1 As (
 21                   Select Areaid, goodsid, guideprice From   @ Dt 
 22                  Union   All 
 23                   Select A. areaid, T. goodsid, T. guideprice From Md_area Inner   Join   @ Dt T
 24                       On A. quotationtype = T. areaid
 25                       And   Exists (Select   0   From Md_goods g Where G. typeid = A. goodstypeid And G. ID = T. goodsid)
 26 )
 27           Insert   Into   @ Dt_prices   Select Areaid, goodsid, guidepriceFrom Temp_1;
 28               Update Up Set Up. guideprice = TP. guideprice From Md_updateprice up Inner   Join    @ Dt_prices TP On Up. areaid = TP. areaid And Up. goodsid = TP. goodsid
 29               And   Convert ( Varchar ( 10 ), Up. pricedate, 120 ) =  Convert ( Varchar ( 10 ), Getdate (), 120 )
 30                  If ( @ Error  <>  0 ) Goto Error_handler;
 31           Set   @ Errorsmall  =-  2 ;
 32               Insert   Into Md_updateprice (areaid, goodsid, guideprice, pricedate)
33                   Select T. * , Getdate () From   @ Dt_prices T Where   Not   Exists ( Select   0   From Md_updateprice up Where Up. areaid = T. areaidAnd Up. goodsid = T. goodsid)
 34                   If ( @ Error  <>  0 ) Goto Error_handler;
 35           Set   @ Errorsmall  =-  3 ;
36               Update H Set H. guideprice = T. guideprice From Md_historyprices H, @ Dt_prices T Where H. goodsid = T. goodsid
 37                    And H. areaid = T. areaid And   Convert (Varchar ( 10 ), H. pricedate, 120 ) =  Convert ( Varchar ( 10 ), Getdate (), 120 );
 38                   If ( @ Error  <> 0 ) Goto Error_handler;
 39           Set   @ Errorsmall  =-  4 ;
 40               Update P Set P. guideprice = TT. guideprice From Md_prices P Inner  Join   @ Dt_prices TT
 41                    On P. goodsid = TT. goodsid And 
 42 P. areaid = TT. areaid And   Convert ( Varchar ( 10 ), P. pricedate, 120 )=  Convert ( Varchar ( 10 ), Getdate (), 120 );
 43           If ( @ Error  <>  0 ) Goto Error_handler;
 44      Commit   Tran ;
 45       Return   0 ;
 46 Error_handler:
 47       Rollback   Tran ;
 48       Return   @ Errorsmall ;
 49   End 
50  
 51  
 52  
 53   Go 

We can see that a table variable is defined in the above stored procedure,

Declare @ dt_prices table (areaid int not null, goodsid int not null, guideprice decimal (18, 4) not null );

The table variable @ dt_prices has three columns: areaid, goodsid, and guideprice. Their types are integer, integer, and double-precision. They are not empty;

We can see that this table variable @ dt_prices is used in the following functional modules.

For the above stored procedure, I defined a table value variable ty_publictodayprice. The syntax for creating this table value variable is:

Table value variable

 1   Use   [ Xb_quotation_dev2  ] 
 2   Go 
 3  
 4   /*  * *** Object: userdefinedtabletype [DBO]. [ty_publictodayprice] script Date: 02/18/2012 14:43:42 *****  */ 
 5   Create Type [  DBO  ] .[  Ty_publictodayprice  ]   As   Table (
 6       [  Areaid  ]   [  Int  ]   Not   Null ,
 7       [  Goodsid ]   [  Int  ]   Not   Null ,
 8       [  Guideprice  ]   [  Decimal  ] ( 18 , 4 ) Null 
 9 )
 10   Go