有关@@tancount的问题

[zhiguo2008]

当前的问题提示是:
EXECUTE 后的事务计数指出缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。原计数 = 0，当前计数 = 1
我想知道怎样在存储过程中设置

@@trancount

计数器为0

[hery2002]

这个不是设置的,
是你的事务没有匹配,
BEGIN TRAN之后没有 COMMIT 或者 ROLLBACK.

[Limpire]

EXECUTE里面有显式事务，没有提交或回滚

[hery2002]

看看这个:
存储过程中的COMMIT问题
http://topic.csdn.net/t/20030418/16/1679341.html

[zhiguo2008]

代码在这里,帮忙看一下.

/*更新按客户出货记录*/

create procedure  uCustomerGoods_UpdatedCustomerGoods(@id int,@num numeric(18,0),@customerid int,@machineid nvarchar(100),@date datetime,@storageid int,@remark Text,@execute nvarchar(1000) output,@recordid int output)
as
BEGIN
 declare @point1 int,@point2 int,@point3 int,@point4 int,@point5 int,@point6 int,@point7 int,@point8 int
 declare @innermachineid nvarchar(100),@innercustomerid int,@innerdate datetime,@innerNum int
 declare @innerparentMachineid nvarchar(100),@updateParentMachineid nvarchar(100)--更改后机型的父机型ID
 
 begin tran 
--记录原来的机型ID
select @innermachineid=machineid,@innercustomerid=customerid,@innerdate=[date],@innerNum=num from detailcustomergoods where id=@id
--是否原来的记录有父记录
if(charindex('主板',@innermachineid)>0 or charindex('整机',@innermachineid)>0 or charindex('模块',@innermachineid)>0)
begin
--取得此子机型的父机型 
select  @innerparentMachineid=id from machines where id=(select ltrim(rtrim(parentid)) from machines where id=@innermachineid)
--删除原来记录对应的父机型记录对应的入库记录
delete from storagemachines where recordid=(select id from detailcustomergoods where customerid=@innercustomerid and machineid=@innerparentmachineid and [date]=@innerdate and num=@innernum)
 --设定是否删除成功标志
 set @point1=@@rowcount
 print @point1
--删除父机型记录
delete from detailcustomergoods where customerid=@innercustomerid and machineid=@innerparentmachineid and [date]=@innerdate and num=@innernum
 set @point2=@@rowcount
 print @point2
end 
--不管有没有父机型，都要删除原来的子机型记录
--删除子机型入库记录
delete from storagemachines where recordid=@id
 set @point3=@@rowcount
 print @point3
--删除子机型记录
delete from detailcustomergoods where id =@id
  set @point4=@@rowcount
 print @point4
/*处理更新后的机型记录*/
--判断更新后的记录是否有重复customerid,machineid,date三者相同,有则回滚
if exists(select 1 from detailcustomergoods where customerid=@customerid and machineid=@machineid and [date]=@date)
  begin
  rollback tran 
  set @execute='数据库已经有此记录，请重新核对后再修改!'--返回出错的标志位
  end
--更新后的机型记录有父机型记录情况
if(charindex('主板',@machineid)>0 or charindex('整机',@machineid)>0 or charindex('模块',@machineid)>0)
 begin
   --更改后的父机型记录ID
 select @updateParentMachineid=parentid from machines where id=@machineid
  --插入父机型记录
  insert into detailcustomergoods values(@customerid,@updateParentMachineid,@num,@date,@storageid,@remark)
  set @point5=@@rowcount
  print @point5
  --插入父机型的入库记录
  insert into storagemachines values(@storageid,@updateParentMachineid,-@num,@date,0,@@identity)
  set @point6=@@rowcount
print @point6
 end
--不管包不包含父机型，新机型都要插入与入库
  --插入改新的机型记录
  insert into detailcustomergoods values(@customerid,@machineid,@num,@date,@storageid,@remark)
 set @point7=@@rowcount
 set @recordid=@@identity
 print @point7
 --新的机型记录入库
  insert into storagemachines values(@storageid,@machineid,-@num,@date,0,@@identity)
  set @point8=@@rowcount
  print @point8
--判断各SQL语句的执行标志位，看是不是成确执行
if(charindex('主板',@innermachineid)>0 or charindex('整机',@innermachineid)>0 or charindex('模块',@innermachineid)>0)--
 begin
   if(@point1!=1 or @point2!=1 )--如果没有正确的被删除
     begin 
      set @execute='更新父机型记录时发生错误'
    rollback tran 
      end  
 end
else if(charindex('主板',@machineid)>0 or charindex('整机',@machineid)>0 or charindex('模块',@machineid)>0)
  begin
   if(@point5!=1 or @point6!=1)
     begin 
      set @execute='更新父机型记录时发生错误'
    rollback tran 
     end
  end

else if(@point3!=1 or @point4!=1 or @point7!=1 or @point8!=1)
  begin
    set @execute='更新原始数据时发生错误'
    rollback tran
  end
 --提交事务
else 
 begin
   set @execute='1'
   commit tran 
 end

END

[utpcb]

MARK

[zhiguo2008]

修改了一下.好了.

/*更新按客户出货记录*/

create procedure  uCustomerGoods_UpdatedCustomerGoods(@id int,@num numeric(18,0),@customerid int,@machineid nvarchar(100),@date datetime,@storageid int,@remark Text,@execute nvarchar(1000) output,@recordid int output)
as
BEGIN
 declare @point1 int,@point2 int,@point3 int,@point4 int,@point5 int,@point6 int,@point7 int,@point8 int
 declare @innermachineid nvarchar(100),@innercustomerid int,@innerdate datetime,@innerNum int
 declare @innerparentMachineid nvarchar(100),@updateParentMachineid nvarchar(100)--更改后机型的父机型ID
 print '当前的计数器值为:'+ Convert(nvarchar,@@trancount)
 begin tran 
 set nocount on--不返回受影响的行数.
 print 'begin tran 后的@@trancount值为:'+Convert(nvarchar,@@trancount)
--记录原来的机型ID
select @innermachineid=machineid,@innercustomerid=customerid,@innerdate=[date],@innerNum=num from detailcustomergoods where id=@id
--是否原来的记录有父记录
if(charindex('主板',@innermachineid)>0 or charindex('整机',@innermachineid)>0 or charindex('模块',@innermachineid)>0)
begin
--取得此子机型的父机型 
select  @innerparentMachineid=id from machines where id=(select ltrim(rtrim(parentid)) from machines where id=@innermachineid)
--删除原来记录对应的父机型记录对应的入库记录
delete from storagemachines where recordid=(select id from detailcustomergoods where customerid=@innercustomerid and machineid=@innerparentmachineid and [date]=@innerdate and num=@innernum)
 --设定是否删除成功标志
 set @point1=@@rowcount

--删除父机型记录
delete from detailcustomergoods where customerid=@innercustomerid and machineid=@innerparentmachineid and [date]=@innerdate and num=@innernum
 set @point2=@@rowcount

end 
--不管有没有父机型，都要删除原来的子机型记录
--删除子机型入库记录
delete from storagemachines where recordid=@id
 set @point3=@@rowcount

--删除子机型记录
delete from detailcustomergoods where id =@id
  set @point4=@@rowcount

/*处理更新后的机型记录*/
--判断更新后的记录是否有重复customerid,machineid,date三者相同,有则回滚
if exists(select 1 from detailcustomergoods where customerid=@customerid and machineid=@machineid and [date]=@date)
  begin 
  set @execute='数据库已经有此记录，请重新核对后再修改!'--返回出错的标志位
  print '发生数据重复后的计数:'+Convert(nvarchar,@@trancount)
  goto  QuitWithRollback
 
  end
--更新后的机型记录有父机型记录情况
if(charindex('主板',@machineid)>0 or charindex('整机',@machineid)>0 or charindex('模块',@machineid)>0)
 begin
   --更改后的父机型记录ID
 select @updateParentMachineid=parentid from machines where id=@machineid
  --插入父机型记录
  insert into detailcustomergoods values(@customerid,@updateParentMachineid,@num,@date,@storageid,@remark)
  set @point5=@@rowcount

  --插入父机型的入库记录
  insert into storagemachines values(@storageid,@updateParentMachineid,-@num,@date,0,@@identity)
  set @point6=@@rowcount

 end
--不管包不包含父机型，新机型都要插入与入库
  --插入改新的机型记录
  insert into detailcustomergoods values(@customerid,@machineid,@num,@date,@storageid,@remark)
 set @point7=@@rowcount
 set @recordid=@@identity

 --新的机型记录入库
  insert into storagemachines values(@storageid,@machineid,-@num,@date,0,@@identity)
  set @point8=@@rowcount
 print @point1  print @point2 print @point3 print @point4 print @point5 print @point6 print @point7 print @point8
--判断各SQL语句的执行标志位，看是不是成确执行
if(charindex('主板',@innermachineid)>0 or charindex('整机',@innermachineid)>0 or charindex('模块',@innermachineid)>0)--
 begin
   if(@point1!=1 or @point2!=1 )--如果没有正确的被删除
     begin 
      set @execute='更新父机型记录时发生错误'
      goto  QuitWithRollback
     end  
 end
 if(charindex('主板',@machineid)>0 or charindex('整机',@machineid)>0 or charindex('模块',@machineid)>0)
  begin
   if(@point5!=1 or @point6!=1)
     begin 
      set @execute='更新父机型记录时发生错误'
      goto  QuitWithRollback
     end
  end

 if(@point3!=1 or @point4!=1 or @point7!=1 or @point8!=1)
  begin
    set @execute='更新原始数据时发生错误'
    goto  QuitWithRollback
  end
 --提交事务
   set @execute='1' 
   print '数据提交后的@@trancount的值'+Convert(nvarchar,@@trancount)
   commit tran 
  goto Endsave
     
 QuitWithRollback: 
 IF   (@@TRANCOUNT   >   0)   ROLLBACK   TRANSACTION 
 EndSave:

 set   nocount   off   
     
END