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

经常遇见过这种情况，在存储过程中经常用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(@@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.AreaID AND 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

可以看到这上面的存储过程中，定义一个表变量，

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