表變數在預存程序或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