SQL Server 游标应用小例子

 //声明临时表

create table #temp(

ID int,

SignetState int, 

SignetRegisterID INT 

)

declare @sql varchar(max)

set @sql='

insert into #temp

 SELECT ID,SignetState,SignetRegisterID FROM OASignet' 

 set @sql+=' '

exec(@sql)

DECLARE @id INT 

declare @SignetState INT

DECLARE @SignetRegisterID INT 

//声明游标  

declare cur1 cursor for select ID,SignetState,SignetRegisterID from  #temp

open cur1   //打开游标

fetch next from cur1 into @id,@SignetState,@SignetRegisterID //遍历下一条数据

while @@fetch_status=0 （0：fetch语句成功；-1：fetch语句失败或行不在结果集中；-2:提取的行不存在）

begin

 

if @SignetState=2 

DECLARE @RegisterID INT 

SELECT @RegisterID=max(id) FROM OASignetRegister WHERE SignetID=@id

update OASignet set  SignetRegisterID=@RegisterID

where id=@id

 

fetch next from cur1 into @id,@SignetState,@SignetRegisterID 

end

close cur1//关闭游标

deallocate cur1//删除游标

select * from #temp

DROP TABLE #temp