表变量在存储过程或sql server中的运用

g87616758

　　经常遇见过这种情况，在存储过程中经常用with创建临时表，这个临时表这这个功能模块中多次运用，如果都用With创建相同功能的临时表，好性能和存储过程中有很多冗余代码，为此，我用表变量来实现此种功能(不是表值变量)
　　定义表变量的语法：
　　DECLARE 表变量名字 TABLE(
　　列名 列类型 是否为空,
　　列名 列类型 是否为空,
　　列名 列类型 是否为空，
　　列名 列类型 是否为空
　　)
　　下面是我这项目中运用的存储过程：


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,GuidePrice DECIMAL(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 a 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,GuidePrice FROM 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(@@ERROR0) 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.AreaID AND up.GoodsId= t.GoodsId)   
34                 IF(@@ERROR0) 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(@@ERROR0) 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(@@ERROR0) 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
　　可以看到这上面的存储过程中，定义一个表变量，
　　DECLARE @dt_Prices TABLE(AreaID INT NOT NULL ,GoodsId INT NOT NULL,GuidePrice DECIMAL(18,4) NOT NULL );
　　此表变量@dt_Prices 有3列，分别是AreaID,GoodsId,GuidePrice，他们类型分别是整形，整形，双精度，都是非空；
　　
可以看到在下面的功能模块中我分别用到这个表变量@dt_Prices
　　对于上面的存储过程，我定义了一个表值变量Ty_PublicTodayPrice，创建这个表值变量的语法是：


表值变量


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