MSSQL中循环表

在很多ERP中进行表循环，是非常简单的。如QAD中用For each table就可以循环表，AX中用While select table。

在MSSQL中，可以通过两种方式进行表循环。

1、通过游标。　

 1 declare @result table
 2 (
 3  custid int,
 4  ordermonth datetime,
 5  qty int,
 6  runqty int,
 7  primary key(custid,ordermonth)
 8 );
 9 
10 declare 
11   @custid as int,
12   @prvcustid as int,
13   @ordermonth as datetime,
14   @qty as int,
15   @runqty as int;
16 declare c cursor fast_forward for    --定义游标
17 select custid,ordermonth,qty
18 from Sales.CustOrders
19 order by custid,ordermonth;
20 
21 open c    --打开游标
22 fetch next from c into @custid,@ordermonth,@qty;
23 select @prvcustid = @custid,@runqty = 0;
24 while @@fetch_status = 0    --当不是最后一行记录时，函数返回0
25 begin
26     --Do something 
27 end
28 close c;    --关闭游标
29 deallocate c;    --释放游标

2、通过特定ID。

 1 declare @max int
 2 declare @i int
 3 select ROW_NUMBER() over (order by UnitId) as 'Id',RECID into #temp from UNIT
 4 select @max=max(Id) from #temp
 5 set @i = 1
 6 while (@i <= @max)
 7 begin
 8 if @i = 10
 9 begin
10 select * from UNIT where RECID = (select RECID from #temp where ID = @i) --11125166
11 break
12 end
13 set @i = @i + 1
14 end
15 
16 drop table #temp