错误提示 :EXECUTE 后的事务计数指示缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句

SQL code

CREATE PROCEDURE pr_getjyh  @in_pzh char(10),@in_gysh char(6) , @rval integer OUTPUT 
AS
DECLARE @rq datetime
DECLARE @spbh char(10)
DECLARE @gysh char(6)
DECLARE @ggxh char(40)
DECLARE @weight decimal(5, 2)
DECLARE @Code char(30)
DECLARE @spmc char(30)

DECLARE csr_getjyh CURSOR FOR SELECT rq,spbh,spmc,gysh, ggxh1, weight  FROM f_jdtzd where pzh=@in_pzh Order by spbh
select @rval = -1    
begin transaction tran_jyhsh
open csr_getjyh
fetch next from csr_getjyh into @rq, @spbh,@spmc,@gysh,@ggxh,@weight
while (@@fetch_status<>-1)
begin
        Select TOP 1  @Code =Code From Temp_HJJYH Where bz='0' order by id
    IF @@ERROR<>0
    begin
        rollback transaction tran_jyhsh
        close csr_getjyh
        deallocate csr_getjyh
        print '检验号已用完'
        select @rval = -10
        return
    end

        if not exists(select 1 from HJJYH where Code=@Code) 
       begin
        Insert into HJJYH( Spbh, Code, GoldName, Number, Weight) values(@spbh,@Code,@spmc,@ggxh,@weight)
        IF @@ERROR<>0
        begin
            rollback transaction tran_jyhsh
            close csr_getjyh
            deallocate csr_getjyh
            print @Code +  '插入HJJYH表失败'
            select @rval = -12
            return
        end

                update Temp_HJJYH Set bz='1' where Code=@Code
        IF @@ERROR<>0
        begin
            rollback transaction tran_jyhsh
            close csr_getjyh
            deallocate csr_getjyh
            print @Code +  '更新Temp_HJJYH已使用标记失败'
            select @rval = -12
            return
        end
            end
        else
        begin
        rollback transaction tran_jyhsh
        close csr_getjyh
        deallocate csr_getjyh
        print '此检验号已使用'+@Code
        select @rval = -11
        return
        end
fetch next from csr_getjyh into @rq, @spbh,@spmc,@gysh,@ggxh,@weight
end


commit transaction tran_jyhsh
close csr_getjyh
deallocate csr_getjyh
select @rval=0
GO

没有细化的学习过,请专业人士帮忙查看下我这存储过程有哪些问题,请指出并给予正确的写法,万分感谢!

以后编译可以通过 不知道逻辑上哪里出了问题。

写的太繁琐，不必循环加游标。

设计是这样的:
1.先判断Temp_HJJYH表中的检验号是否用完(已使用过的bz=1),已用完则回滚
2.在插入HJJYH表前先判断要插入的检验号在HJJYH表中是否已存在,存在则回滚
3.HJJYH表中记录插入成功后更新Temp_HJJYH表中已用的检验号bz=1,更新出错则回滚

这之前还报其他错吗？

错误是出现在执行这段代码时无法回滚(当HJJYH表中已经存在此Code时让他回滚)
begin
  rollback transaction tran_jyhsh
  close csr_getjyh
  deallocate csr_getjyh
  print '此检验号已使用'+@Code
  select @rval = -11
  return
 end
代码哪里有错误呢?请高手指点

刚才去试了下,好象都回滚不了,哪位能按照我3楼所写的设计原理 重写下这个存储过程,真得无从下手了

感觉Select TOP 1 @Code =Code From Temp_HJJYH Where bz='0' order by id
  IF @@ERROR<>0
这样判断是不成立的,也应该用
  IF not exists(Select TOP 1 @Code =Code From Temp_HJJYH Where bz='0' order by id
) 判断才对

看不出有什么错啊！
执行过程中有报错信息吗？

这是我新整理的,好象也还是不能回滚


CREATE PROCEDURE pr_getjyh @in_pzh char(10),@in_gysh char(6) , @rval integer OUTPUT 
AS
DECLARE @rq datetime
DECLARE @spbh char(10)
DECLARE @gysh char(6)
DECLARE @ggxh char(40)
DECLARE @weight decimal(5, 2)
DECLARE @Code char(30)
DECLARE @spmc char(30)

DECLARE csr_getjyh CURSOR FOR SELECT rq,spbh,spmc,gysh, ggxh1, weight FROM f_jdtzd where pzh=@in_pzh Order by spbh
select @rval = -1  
begin transaction tran_jyhsh
open csr_getjyh
fetch next from csr_getjyh into @rq, @spbh,@spmc,@gysh,@ggxh,@weight
while (@@fetch_status<>-1)
begin
  if not exists(Select TOP 1 @Code =Code From Temp_HJJYH Where bz='0' order by id)
  begin
  rollback transaction tran_jyhsh
  close csr_getjyh
  deallocate csr_getjyh
  print '检验号已用完'
  select @rval = -10
  return
  end

  if exists(select 1 from HJJYH where Code=@Code) 
  begin
  rollback transaction tran_jyhsh
  close csr_getjyh
  deallocate csr_getjyh
  print '此检验号已使用'+@Code
  select @rval = -11
  return
  end

  Insert into HJJYH( Spbh, Code, GoldName, Number, Weight) values(@spbh,@Code,@spmc,@ggxh,@weight)
  IF @@ERROR<>0
  begin
  rollback transaction tran_jyhsh
  close csr_getjyh
  deallocate csr_getjyh
  print @Code + '插入HJJYH表失败'
  select @rval = -12
  return
  end

  update Temp_HJJYH Set bz='1' where Code=@Code
  IF @@ERROR<>0
  begin
  rollback transaction tran_jyhsh
  close csr_getjyh
  deallocate csr_getjyh
  print @Code + '更新Temp_HJJYH已使用标记失败'
  select @rval = -12
  return
  end

fetch next from csr_getjyh into @rq, @spbh,@spmc,@gysh,@ggxh,@weight
end




commit transaction tran_jyhsh
close csr_getjyh
deallocate csr_getjyh
select @rval=0
GO