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