execute后的事务计数指出缺少了commit或rollback transaction 语句。原计数=0,当前计数=1

goodSmile 2006-12-14 10:43:03
execute后的事务计数指出缺少了commit或rollback transaction 语句。原计数=0,当前计数=1,请问这是什么错误 啊,下面是我写的存储过程,请大家帮帮忙。

ALTER PROCEDURE dbo.alterSFQD
(
@id int,
@bid char(10),
@jb int,
@rq datetime = null,
@szdd varchar(50) = null,
@dwid int,
@hkid int,
@sycjs decimal = null,
@bycjs decimal,
@ysl decimal = null,
@dj money,
@je money = null,
@sfhb char(4) = null,
@sfyfb char(4),
@bz ntext = null,
@maxvalue decimal = null,
@result varchar(50) output,
@count int = null
)
AS
set @rq = getdate()
if @sycjs < @bycjs
begin
set @ysl = @bycjs - @sycjs
set @je = @ysl * @dj
set @sfhb = '否'
end
else
begin
set @maxvalue = (select MaxValue from waterbiao where bid = @bid)
set @ysl = @maxvalue - @sycjs + @bycjs
set @je = @ysl * @dj
set @sfhb = '是'
end
begin transaction
update sfqd
set bid=@bid, rq = @rq,szdd = @szdd,dwid = @dwid,hkid = @hkid,bycjs = @bycjs,ysl = @ysl,dj = @dj,je = @je,sfhb = @sfhb,sfyfb = @sfyfb,bz = @bz
where id = @id

set @count = (select count(id) from sfqd where bid=@bid and year(rq)=year(getdate()) and month(rq)=month(getdate()))
if @count > 1
begin
rollback tran
set @result = '此表卡号已录入,更新错误'
end
else
begin
set @result = '该记录已更新'
return
end
commit transaction
...全文
346 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
goodSmile 2006-12-14
  • 打赏
  • 举报
回复
leo_lesley(leo) ,谢谢了啊,恩,可以通过了
leo_lesley 2006-12-14
  • 打赏
  • 举报
回复
ALTER PROCEDURE dbo.alterSFQD
(
@id int,
@bid char(10),
@jb int,
@rq datetime = null,
@szdd varchar(50) = null,
@dwid int,
@hkid int,
@sycjs decimal = null,
@bycjs decimal,
@ysl decimal = null,
@dj money,
@je money = null,
@sfhb char(4) = null,
@sfyfb char(4),
@bz ntext = null,
@maxvalue decimal = null,
@result varchar(50) output,
@count int = null
)
AS
set @rq = getdate()
if @sycjs < @bycjs
begin
set @ysl = @bycjs - @sycjs
set @je = @ysl * @dj
set @sfhb = '否'
end
else
begin
set @maxvalue = (select MaxValue from waterbiao where bid = @bid)
set @ysl = @maxvalue - @sycjs + @bycjs
set @je = @ysl * @dj
set @sfhb = '是'
end
begin transaction
update sfqd
set bid=@bid, rq = @rq,szdd = @szdd,dwid = @dwid,hkid = @hkid,bycjs = @bycjs,ysl = @ysl,dj = @dj,je = @je,sfhb = @sfhb,sfyfb = @sfyfb,bz = @bz
where id = @id

set @count = (select count(id) from sfqd where bid=@bid and year(rq)=year(getdate()) and month(rq)=month(getdate()))
if @count > 1
begin
rollback tran
set @result = '此表卡号已录入,更新错误'
end
else
begin
commit transaction
set @result = '该记录已更新'
return
end

提交事务应该在begin ... end 里吧
leo_lesley 2006-12-14
  • 打赏
  • 举报
回复
ALTER PROCEDURE dbo.alterSFQD
(
@id int,
@bid char(10),
@jb int,
@rq datetime = null,
@szdd varchar(50) = null,
@dwid int,
@hkid int,
@sycjs decimal = null,
@bycjs decimal,
@ysl decimal = null,
@dj money,
@je money = null,
@sfhb char(4) = null,
@sfyfb char(4),
@bz ntext = null,
@maxvalue decimal = null,
@result varchar(50) output,
@count int = null
)
AS
set @rq = getdate()
if @sycjs < @bycjs
begin
set @ysl = @bycjs - @sycjs
set @je = @ysl * @dj
set @sfhb = '否'
end
else
begin
set @maxvalue = (select MaxValue from waterbiao where bid = @bid)
set @ysl = @maxvalue - @sycjs + @bycjs
set @je = @ysl * @dj
set @sfhb = '是'
end
begin transaction
update sfqd
set bid=@bid, rq = @rq,szdd = @szdd,dwid = @dwid,hkid = @hkid,bycjs = @bycjs,ysl = @ysl,dj = @dj,je = @je,sfhb = @sfhb,sfyfb = @sfyfb,bz = @bz
where id = @id

set @count = (select count(id) from sfqd where bid=@bid and year(rq)=year(getdate()) and month(rq)=month(getdate()))
if @count > 1
begin
rollback tran
set @result = '此表卡号已录入,更新错误'
end
else
begin
set @result = '该记录已更新'
return
end

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧