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,assdate,
@message OUTPUT,@result OUTPUT
if @result=0
commit tran
else
rollback transaction mypoint
select result=@result,message=@message
我第一次执行时有时出错,有时不出错,第二次执行时就会出错!为什么?
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,assdate,
@message OUTPUT,@result OUTPUT
if @result=0
commit tran
else
rollback transaction mypoint
select result=@result,message=@message
我第一次执行时有时出错,有时不出错,第二次执行时就会出错!为什么?