//声明临时表 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 |
|