超过30分钟修改状态

最新推荐文章于 2019-12-27 09:40:18 发布

原创最新推荐文章于 2019-12-27 09:40:18 发布 · 197 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Go

存储过程专栏收录该内容

1 篇文章

订阅专栏

if exists(select * from dbo.sysobjects 
where id=object_id('t_updateNumberStatus') and objectproperty(id,'isprocedure')=1)
drop procedure t_updateNumberStatus
go

create proc t_updateNumberStatus as

 declare @numberCursor cursor --修改结果集
 declare @id varchar(50) --表的id
 declare @status int --状态
 declare @estatus int --改后状态

Begin Tran

	set @estatus=1  --将改后状态设置成1
	set @numberCursor=cursor for --状态为2（时间超过30分钟的）的结果集
		select id,status from tb_entity_number where  
		status=2 and flag=1 and datediff(mi,yz_time,getdate()) > 30

	open @numberCursor
		fetch next from @numberCursor into @id,@status
	while(@@fetch_status=0)
		begin	
			
		update  tb_entity_number set status=@estatus where id=@id --更新表
	
				
 	If @@Error <> 0   
	    Begin  
	    Rollback Tran   
	    Close @numberCursor   
	    Deallocate @numberCursor   
	    Return -1   
	    End  

      fetch next from @numberCursor into @id,@status
		
End  
close @numberCursor --关闭游标
deallocate @numberCursor --删除游标
Commit Tran  

--EXECUTE  t_updateNumberStatus