紧急求救:SQL SERVER的事务处理问题(150分)

  • 主题发起人 主题发起人 once
  • 开始时间 开始时间
O

once

Unregistered / Unconfirmed
GUEST, unregistred user!
现在我有一个SQL SERVER的PROCEDURE,在执行时老是出错,错误提示如下:
general sql error:transaction count after execute indicates that
a commit or rollback transaction is missing. previous count=1,current count=2.
我的procedure是这样写的:
/*新批执业处理存储过程*/
CREATE PROCEDURE lawyer_New_pass
@aid integer,
@PassDate datetime,
@message varchar(60) OUTPUT,/*返回出错时的出错提示信息*/
@result int OUTPUT/*执行成功/失败的返回值0---成功1---失败*/

AS
declare @certNo4 char(12),
@certNo3 char(8),
@certNo2 char(7),
@certNo1 char(6),
@lid integer,
@firmAbbr varchar(16),
@area char(4),
@areacode char(2),
@certType char(4),
@IssueType char(4),
@regiyear int,
@sex char(2),
@certId int,
@fid int,
@count int,
@I int,
@j int
begin
/*开始一个事务*/
begin tran mytran
save transaction mysavepoint

select @lid=lid,@certtype=applytype,@firmabbr=applyfirm,@area=area
from lawyerapply
where id=@aid

select @fid=fid
from firms
where abbr=@firmabbr


select @sex=sex
from lawyers
where lid=@lid

select @areacode=code
from code2
where name=@area

/*为新批执业律师增加一条执业经历,‘开始时间’为@passdate,‘开始类型’为'新发',
‘事务所字号’为@FIRMABBR,‘执业证类型’@CERTTYPE,‘是否为目前最后一条执业经历’
为'1',是否已删除为'0'(未删除)*/
insert into lawyhis(lid,startdate,firmabbr,certtype,startreason,lastrecord,deleted)
values(@lid,@passdate,@firmabbr,@certtype,'新批','1','0')

/*如果最后一次操作失败,则数据库回滚,返回*/
IF @@ERROR<>0 BEGIN
select @result=1
select @message='数据库有错误!'
rollback transaction mysavepoint
return
END
/*如果该名律师是首次入库的话,那么对于他的执业经历的处理应该何新批登记一样,仅仅是
增加一条执业记录,如果该律师事先已入库了的话,那么应该填完上一条执业经历,再
为该律师生成一条执业证记录,批准时间为@PASSDATE,执业证类型为@certtype;
颁发类型为‘新发’,'是否已颁发执业证’为‘0'(未颁发),'是否已删除'为'0'
(未删除) */

/*插入一条记录到执业证情况表中*/
insert into certhis(lid,permitdate,type,issuetype,issuecer,deleted)
values(@lid,@passdate,@certtype,'新发','0','0')

/*如果最后一次操作失败,则数据库回滚,返回*/
IF @@ERROR<>0 BEGIN
select @result=1
select @message='数据库有错误!'
rollback transaction mysavepoint
return
END
/*获取当前插入到执业证情况表的记录的ID*/
select @I=@@Identity
/*为新批律师生成执业证编码
律师执业证编号: 09BBCCDEFFFF
BB:区码;
CC:发证年份;
D:执业证类别代码;
E:性别代码;
FFFF:执业证序列号;*/

/*获取该名人员的性别*/

select @certNo1='09'+@areacode+right(str(datepart(yy,@passdate),4),2)/*未写完*/

/*如果执业证类别必须是专职或兼职,否则执业证编码中执业证类别代码位为空格*/
if @certtype='专职'
select @certNo2=@certNo1+'1'
else if @certtype='兼职'
select @certNo2=@certNo1+'2'
else
select @certNo2=@certNo1+' '

/*性别必须是男或女,否则执业证编码中性别代码位为空格*/
if @sex='女'
select @certNo3=@certNo2+'2'
else if @sex='男'
select @certNo3=@certNo2+'1'
else
select @certNo3=@certNo2+' '

/*将执业证流水号加1*/
update serial
set certserial=certserial+1

/*获取执业证流水号*/
select @certId=certSerial
from serial


/*将流水好转化为四位的规则字符串(左边用零填补)*/
if @certId<10
select @certNo4=@certNo3+'000'+str(@certId,1)

else if @certId>=10 and @certId<100
select @certNo4=@certNo3+'00'+STR(@certId,2)
else if @certId>=100 and @certId<1000
select @certNo4=@certNo3+'0'+str(@certId,3)
else if @certId>=1000 and @certId<10000
select @certNo4=@certNo3+str(@certId,4)
else if @certId>=10000 begin
select @message='序列号不对(》10000)'
select @result=1
ROLLBACK transaction mysavepoint
return
end
/*判断执业证编码中是否有空格存在,如果有空格的话,表示数据库中该名人员的信息中有误
接下来分析错误的地方并提示可能的错误原因*/
select @j=1
while (substring(@certNO4,@j,1)<>' ') and @j<13
select @j=@j+1
if @j<5 begin
select @message='区码不对,请检查区县或者区县代码是否为空或不正确!'

select @result=1
ROLLBACK transaction mysavepoint
return
end
else if @j<8 begin
select @message='执业类别为空或不正确!'
select @result=1
ROLLBACK transaction mysavepoint
return
end
else if @j<9 begin
select @message='性别为空或不正确!'
select @result=1
ROLLBACK transaction mysavepoint
return
end
else if @j<13 begin
select @message='序列号未初始化或不正确!'
select @result=1
ROLLBACK transaction mysavepoint
return
end

/*执业证号码无误,将其写入执业证情况表*/
Update certhis
set certificateNo=@certNO4
where id=@I
/*如果最后一次操作失败,则数据库回滚,返回*/
IF @@ERROR<>0 BEGIN
select @result=1
select @message='数据库有错误!'
rollback transaction mysavepoint
return
END
/*为新批律师注册,注册日期为@passdate,注册类型为'新注',注册情况为'是',如果
库中已有该律师的有效注册信息,则将这些(一条或多条)有效注册信息改为无效,
将这条新增的注册信息的是否有效注册信息为'1'*/

select @regiyear=registeryear
from serial

insert into regihis(lid,fid,firmname,regidate,register,regiType,deleted,regiyear,certtype)
values(@lid,@fid,@firmabbr,@passdate,'是','新注','0',@regiyear,@certtype)
/*如果最后一次操作失败,则数据库回滚,返回*/
IF @@ERROR<>0 BEGIN
select @result=1
select @message='数据库有错误!'
rollback transaction mysavepoint
return
END
/*更新申报表,设置当前被操作记录的通过位为通过,有效位为无效(无效的申请,因为申请已
通过)*/
update lawyerApply
set passStatus='1',valid='0',passdate=@passdate where id=@aid
/*如果最后一次操作失败,则数据库回滚,返回*/
IF @@ERROR<>0 BEGIN
select @result=1
select @message='数据库有错误!'
rollback transaction mysavepoint
return
END
/*更新人员基本信息表,将相关信息写入*/
update lawyers
set certtype=@certtype,firmabbr=@firmabbr,area=@area,fid=@fid,certNo=@certNo4,firstdate=@passdate,firstcity='上海'
where lid=@lid


/*如果最后一次操作失败,则数据库回滚,返回*/
IF @@ERROR<>0 BEGIN
select @result=1
select @message='数据库有错误!'
rollback transaction mysavepoint
return
END
select @result=0
select @message='新批成功'
commit tran
end


而我是这样调用这个存储过程的:
在一个QUERY里调用存储过程,SQL 语句如下:
declare @result int,
@message varchar(60)
begin tran
save transaction mypoint
EXEC lawyer_new_Pass :aid,:passdate,
@message OUTPUT,@result OUTPUT
if @result=0
commit tran
else
rollback transaction mypoint

select result=@result,message=@message


我第一次执行时有时出错,有时不出错,第二次执行时就会出错!为什么?
 
The reason is that you do not use "rollback transaction" to close
current transac when commit command is not executed.
“rollback transaction mypoint“ just rollback to
the point that was saved the lastest times, not terminate current
transaction. If commit command does not been execute, the transaction
will not end.
 
a commit or rollback transaction is missing 你看不懂吗?:)
反正你的程序我是看不懂。没学过 sql server
加上 commit 提交才能提交数据
delphi 这莫用。
try
query。commit;
exception
query。rollback;
希望对你有所帮助。
 
谢谢诸位!问题的答案我已经找到了!
 
后退
顶部