create PROCEDURE procedure_hrdata_01
AS
BEGIN
BEGIN TRAN --申明事务
--申明游标 为userid
declare order_cursor CURSOR FOR select userid,userdate,jqno from hrdata where bj is null group by userid, userdate,jqno having count(*)>1
declare @a int,@error int,@num int
declare @temp varchar(100),@userid varchar(50),@userdate varchar(50),@jqno varchar(50)--临时变量，用来保存游标值
set @a=1 set @error=0
--打开游标
open order_cursor
WHILE @@FETCH_STATUS = 0 --返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
begin
--开始循环游标变量
FETCH NEXT FROM order_cursor INTO @userid,@userdate,@jqno
--执行sql操作
set @[email protected][email protected][email protected]
select @num=id from hrdata where bj is null and (userid+userdate+jqno) in (@temp) and id not in (select max(id) from hrdata where (userid+userdate+jqno) in (@temp))
update hrdata set bj='11' where bj is null and [email protected]
set @[email protected]+1
set @[email protected]+@@error --记录每次运行sql后 是否正确 0正确
end
if @error=0--没有错误 统一提交事务
begin
commit tran--提交
end
else
begin
rollback tran--回滚
end
CLOSE order_cursor--关闭游标
DEALLOCATE order_cursor--释放游标
END
go
执行:exec procedure_hrdata_01