Use of temporary tables created with Union

I have been writing the stored procedure recently, considering the applicationProgramScalability and performance: a large number of temporary tables are used. Because the performance of creating temporary tables with create table # temp is very poor, we use with to create temporary tables. However, there are many problems with creating temporary tables, for example, when a temporary table is created successfully, insert is not allowed, and order by is not allowed when a temporary table is created with. Here is an insert case;

In my project, the quotation of steel products is designed. Some regions have quotations, some regions have reference quotations, and now you need to obtain the region dt1 with reference to the quotation area, add the region to the map dt1. The stored procedure is listed as follows:

View code

 1   Use   [  Xb_quotation_dev  ] 
 2   Go 
 3  
 4  /*  * *** Object: storedprocedure [DBO]. [usp_addpublishchargetodayprice] script Date: 02/09/2012 23:24:22 *****  */ 
 5   Set Ansi_nulls On 
 6   Go 
 7  
 8   Set Quoted_identifier On 
 9  Go 
 10  
 11   --  ========================================================== =====  
  12   --  Author: <author, Name>  
  13   --  Create Date: <create date,>  
  14  --  Description: <description,>  
  15   --  ========================================================== =====  
  16   Create   Procedure   [  DBO  ] . [  Usp_addpublishchargetodayprice  ] 
 17   @ Areaid      Int ,
 18   @ Typeid      Int 
 19   As 
 20   Begin 
 21           Set Nocount On ;
 22           Declare  @ Errorsum   Smallint   =  0 ;
 23               Begin   Tran ;
 24                   Set   @ Errorsum  =-  1 ;
 25                       Delete   From Charge_md_pricesWhere   Convert ( Varchar ( 10 ), Pricedate, 120 ) <>  Convert ( Varchar ( 10 ), Getdate (), 120 );
 26                       If (@ Error  <>  0 )
 27                           Goto Error_handler;
 28                   Set   @ Errorsum  =-  2 ;
 29                       Delete   From Charge_pricecache Where  Convert ( Varchar ( 10 ), Pricedate, 120 ) <>  Convert ( Varchar ( 10 ), Getdate (), 120 );
 30                       If (@ Error  <>  0 )
 31                           Goto Error_handler;
 32                   Set   @ Errorsum  =-  3 ;
 33                   With Tbarea As 
 34 (
 35                   Select Areaid From Md_area Where Quotationtype =  @ Areaid   And Goodstypeid =  @ Typeid 
 36                   Union  
 37                   Select   @ Areaid  As Areaid
 38 ),
 39 Tbgoods As 
 40 (
 41                   Select ID From Md_goods Where Typeid =  @ Typeid 
 42 )
43                   Insert   Into Charge_md_prices Select   *   From Charge_pricecache
 44                   Where   Convert ( Varchar ( 10 ), Pricedate, 120 ) =  Convert (Varchar ( 10 ), Getdate (), 120 )
 45                         And Areaid In ( Select Areaid From Tbarea) And Goodsid In ( Select ID From Tbgoods)
 46                        If ( @ Error  <>  0 )
 47                           Goto Error_handler;
 48                   Set   @ Errorsum  =-  4 ;
 49                   Update Md_pricegenerationlog Set Publisheddate =  Convert ( Varchar ( 10 ), Getdate (), 120 ), Publishedusername =-  1  
 50                        Where   Convert ( Varchar (10 ), Generatedate, 120 ) =  Convert ( Varchar ( 10 ), Getdate (), 120 )
 51                        If ( @ Error  <>  0 ) Goto Error_handler;
 52               Commit   Tran ;
 53               Return   0 ;
 54 Error_handler:
 55               Rollback   Tran ;
 56               Return  @ Errorsum ;
 57   End 
 58  
 59   Go