With在sql server 2005中的用法

with在MSDN中的讲解，可以参考链接

SQL

 1 ------------------------------------
 2 ------创建表值变量类型--------------
 3 ------------------------------------
 4 CREATE TYPE Ty_NewAreaGoods AS TABLE(
 5 AreaID    INT    NOT NULL,
 6 GoodsID    INT    NOT NULL
 7 )
 8 -------------------------------------
 9 ----创键返回今天报价的数据-----------
10 ----根据有报价的地区获取参-----------
11 ----考报价地区以及交易品id-----------
12 -------------------------------------
13 CREATE PROC [dbo].[usp_GetTodayPrices]
14 @dt Ty_NewAreaGoods READONLY
15 AS
16     BEGIN
17     WITH temp_1 AS(
18          SELECT aa.AreaId,tt.GoodsID AS GoodsID FROM MD_Area aa,@dt tt WHERE aa.QuotationType=tt.AreaID 
19          AND EXISTS(SELECT 0 FROM MD_Goods gg WHERE gg.TypeId = aa.GoodsTypeId AND gg.ID=tt.GoodsID)
20          UNION ALL
21          SELECT AreaID,GoodsID AS GoodsID FROM @dt    
22     )
23     SELECT pp.* FROM MD_Prices pp,temp_1 tt 
24     WHERE pp.AreaID = tt.AreaId AND pp.GoodsId= tt.GoodsID 
25     AND CONVERT(VARCHAR(10),PriceDate,120)=CONVERT(VARCHAR(10),GETDATE(),120)
26     END
27 --------------------------------------
28 ----调用存储过程----------------------
29 ----传入地区ID为1,2;交易品id=34-------
30 --------------------------------------
31 DECLARE @dt Ty_NewAreaGoods;
32 INSERT INTO @dt VALUES(1,34);
33 INSERT INTO @dt VALUES(2,34);
34 EXEC [usp_GetTodayPrices] @dt
35 --------------------------------------

表值变量，with，update结合运用：

Update Code

 1 CREATE PROC [dbo].[usp_UpdatePrice]
 2 @dt Ty_NewAreaGoods READONLY
 3 AS
 4 BEGIN
 5     SET NOCOUNT ON;
 6     DECLARE @ERRORSUM SMALLINT ;
 7     BEGIN TRAN;
 8    SET @ERRORSUM=-1;
 9     WITH temp_1 AS(
10         SELECT aa.AreaId,tt.GoodsID AS GoodsID FROM MD_Area aa,@dt tt WHERE aa.QuotationType=tt.AreaID 
11         AND EXISTS(SELECT 0 FROM MD_Goods gg WHERE gg.TypeId = aa.GoodsTypeId AND gg.ID=tt.GoodsID)
12         UNION ALL
13         SELECT AreaID,GoodsID AS GoodsID FROM @dt   
14     )
15     UPDATE pp SET pp.GuidePrice= 1001 FROM  MD_Prices pp , temp_1 tt 
16     WHERE pp.AreaID = tt.AreaId AND pp.GoodsId = tt.GoodsID
17     IF(@@ERROR<>0) GOTO ERROR_Hander;
18     COMMIT TRAN;
19     RETURN 0;
20 ERROR_Hander:
21     ROLLBACK TRAN;
22     RETURN @ERRORSUM;
23 END
24 
25 
26 DECLARE @dt Ty_NewAreaGoods;
27 INSERT INTO @dt VALUES(2,34);
28 EXEC [usp_UpdatePrice] @dt