With 创建的临时表结合union的运用

近期一直写存储过程，考虑到应用程序扩展性和性能，用到大量的临时表，由于用create table #temp创建临时表性能很差，所以就采用with创建临时表，但是with创建临时有很多问题，比如创建成功后的临时表不能进行insert，用with创建临时表时候不能order by等等；现在举一个insert的情况；

在我的项目中，设计到钢材的报价，有的地区有报价，有的地区有参考报价，现在需要获得有参考有报价地区的地区dt1，并且把该报价地区添加到查询的结果地图地图dt1中；现把该存储过程列举出来：

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_Prices WHERE 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