MSSQL中循环

 

 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

 

3.表

create table ta(id int,name datetime)
declare @i int ,@name datetime
set @i=0;
while @i<10
begin
set @i=@i+1
--insert into ta (id,name) values(@i,DATEADD(hh,@i,GETDATE()))
Select @name=name from ta where id=@i
print @name
end
select * from ta