首页 新闻 搜索 专区 学院

EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 3,当前计数 = 0。

0
悬赏园豆:30 [待解决问题]

--判断此方案是否已流产,如流产给用户退钱 alter proc SP_Buymoney @schemenumber varchar(50), @sysusername varchar(50), @percent decimal(18,2), --要除去的百分比的钱 @queshstate tinyint , --方案表中的撤单状态 @sysdealtype TinyInt, --网站系统保底的交易类型 @dealtype TinyInt --交易类型 as begin --开始事务 declare @intErrorCode int declare @isweb decimal(18,2) declare @userid_system int declare @sysBlance money declare @rebuymoney_self money --方案发起人跟单的钱 declare @rebuymoney_with money --方案跟单人的钱 declare @selfblance money --用户账上的余额 declare @withmoney money --跟单人的钱 declare @count int --跟单人数量 declare @i int=0 declare @BuyDetailID int --跟单人id declare @RelatedUserID int --跟单用户userid begin try begin transaction select @intErrorCode=@@ERROR --每执行一次任务,接收一次错误信息 declare @selfmoney money--自购金额 declare @amout money--购买的总金额 declare @together money --跟单人总金额 select @selfmoney=BuyedMoney from T_Scheme where SchemeNumber=@schemenumber select @together=sum(Price) from T_BuyDetails where SchemeNumber=@schemenumber--跟单人的总金额 set @amout=@together+@selfmoney--已购买总金额 print @amout --已购买总金额 declare @money money--方案总金额 declare @result money --所差金额 select @money=BuyMonery from T_Scheme where SchemeNumber=@schemenumber set @result=@money-@amout --610 --所差金额 print @result --print @result--总金额减去已买金额剩下的钱)(此方案还差的钱)--算还差多少。从保底里减相对应差的钱、如保底不够刚全减走。。 declare @diff money declare @userid int declare @freeze money declare @vacany money --最终空缺的钱。 declare @diffmoney money --标记变量用于退款 select @diff=AssureMonery from T_Scheme where SchemeNumber=@schemenumber --保底的钱 print @diff select @userid=UserID from T_Scheme where SchemeNumber=@schemenumber print @userid select @freeze=Freeze from T_Users where UserID=@userid print @freeze if(@result>=@diff) --空缺所差的钱大于或等于保底金额 begin set @amout=@amout+@diff --加上保底后已买的总金额 select @intErrorCode=@@ERROR set @vacany=@result-@diff --最终空缺的钱(加上保底后还差的钱)。 print @vacany select @intErrorCode=@@ERROR end else --保底的钱大于所差的钱。 begin set @amout=@amout+@result ---所有购买方案的钱(加上保底的) print @amout select @intErrorCode=@@ERROR end declare @Demwer int select @userid_system=UserID from T_Users where Username=@sysusername if(@money<=@amout) begin --可以出票了 set @vacany=0 ---表示不方案总金额与购买金额这间不差钱 set @Demwer=0 update T_Scheme set IsFullCommission=1,QuashStatus=0 where @money<=@amout and SchemeNumber=@schemenumber --可以出票把是否满员:改为已满员 未撤单 ---已满员。把用户购买彩票的钱从冻结金额里面减去,加到系统账户余额里面。 if(@result>=@diff) begin update T_Users set Freeze=Freeze-@diff-@selfmoney where UserID=@userid --冻结金额减去空缺的保底钱 减去自购的钱 set @diffmoney=@diff end else begin update T_Users set Freeze=Freeze-@result-@selfmoney,Balance=Balance+(@diff-@result) where UserID=@userid --冻结金额要减去空缺保底的钱,把多余的钱退还给用户 set @diffmoney=@result --从保底中减去的钱 end --(加上保底后已够方案总金额:满员)发起人跟系统加钱 update T_Users set Balance=Balance+@selfmoney+@diffmoney where Username=@sysusername select @sysBlance=Balance from T_Users where UserID=@userid_system insert into T_UserDealDetails(UserID,DealType,DetailMoney,SchemeNumber,RelatedUserID,NonceBalance) values(@userid_system,@sysdealtype,@vacany,@schemenumber,@userid,@sysBlance) --(加上保底后已够方案总金额:满员)跟单人给系统加钱 select @count= COUNT(ID) from T_BuyDetails where SchemeNumber=@schemenumber print @count set @i=0 while(1=1) begin select @BuyDetailID=ID from T_BuyDetails where id = (select top 1 ID from T_BuyDetails where id not in (select top (@i) ID from T_BuyDetails where SchemeNumber=@schemenumber) and SchemeNumber=@schemenumber) select @RelatedUserID=UserID from T_BuyDetails where id = (select top 1 ID from T_BuyDetails where id not in (select top (@i) ID from T_BuyDetails where SchemeNumber=@schemenumber) and SchemeNumber=@schemenumber) select @withmoney=Price from T_BuyDetails where ID=@BuyDetailID declare @c money select @c= Freeze from T_Users where UserID=@RelatedUserID if (@c-@withmoney)<=0 begin break end print convert(varchar(20),@c)+'-'+convert(varchar(20),@withmoney)+'='+convert(varchar(20),(@c-@withmoney)) --update T_Users set Freeze=Freeze-@withmoney where UserID=@RelatedUserID set @selfblance=0 select @selfblance=Balance from T_Users where UserID=@RelatedUserID insert into T_UserDealDetails (UserID,DealType,DetailMoney,SchemeNumber,RelatedUserID,NonceBalance) values(@RelatedUserID,@dealtype,@withmoney,@schemenumber,@userid,@selfblance) --print @BuyDetailID --print('delete T_BuyDetails where UserID='+@RelatedUserID+' and SchemeNumber='''+@schemenumber+''' and ID='+@BuyDetailID+'') print 'd-e' --print 'delete T_BuyDetails where UserID='+@RelatedUserID+' and SchemeNumber='+@schemenumber+' and ID='+@BuyDetailID+'' set @i=@i+1 if @i=@count begin return end print @i end end else --购买金额不等于方案总金额 begin --判断是否大于90%自己认购金额+跟单金额/总金额 set @isweb=@amout/@money if(@isweb>@percent)--网站保底减钱记录(交易类型为13 ,购买彩票),@vancy begin update T_Users set Balance=Balance-@vacany where UserID=@userid_system select @sysBlance=Balance from T_Users where UserID=@userid_system insert into T_UserDealDetails(UserID,DealType,DetailMoney,SchemeNumber,RelatedUserID,NonceBalance) values(@userid_system,@sysdealtype,@vacany,@schemenumber,@userid,@sysBlance) set @Demwer=0 update T_Scheme set IsFullCommission=1,QuashStatus=0 where @money<=@amout and SchemeNumber=@schemenumber --网站保底后也可以出票把是不否满员:改为已满员 ---已满员。把用户购买彩票的钱从冻结金额里面减去,加到系统账户余额里面。 if(@result>=@diff) begin update T_Users set Freeze=Freeze-@diff-@selfmoney where UserID=@userid --冻结金额减去空缺的保底钱 减去自购的钱 set @diffmoney=@diff end else begin update T_Users set Freeze=Freeze-@result-@selfmoney,Balance=Balance+(@diff-@result) where UserID=@userid --冻结金额要减去空缺保底的钱,把多余的钱退还给用户 set @diffmoney=@result --从保底中减去的钱 end --(加上保底后已够方案总金额:满员)发起人跟系统加钱 update T_Users set Balance=Balance+@selfmoney+@diffmoney where Username=@sysusername select @sysBlance=Balance from T_Users where UserID=@userid_system insert into T_UserDealDetails(UserID,DealType,DetailMoney,SchemeNumber,RelatedUserID,NonceBalance) values(@userid_system,@sysdealtype,@vacany,@schemenumber,@userid,@sysBlance) --(加上保底后已够方案总金额:满员)跟单人给系统加钱 select @count= COUNT(ID) from T_BuyDetails where SchemeNumber=@schemenumber print @count set @i=0 while(1=1) begin select @BuyDetailID=ID from T_BuyDetails where id = (select top 1 ID from T_BuyDetails where id not in (select top (@i) ID from T_BuyDetails where SchemeNumber=@schemenumber) and SchemeNumber=@schemenumber) select @RelatedUserID=UserID from T_BuyDetails where id = (select top 1 ID from T_BuyDetails where id not in (select top (@i) ID from T_BuyDetails where SchemeNumber=@schemenumber) and SchemeNumber=@schemenumber) select @withmoney=Price from T_BuyDetails where ID=@BuyDetailID update T_Users set Freeze=Freeze-@withmoney where UserID=@RelatedUserID set @selfblance=0 select @selfblance=Balance from T_Users where UserID=@RelatedUserID insert into T_UserDealDetails (UserID,DealType,DetailMoney,SchemeNumber,RelatedUserID,NonceBalance) values(@RelatedUserID,@dealtype,@withmoney,@schemenumber,@userid,@selfblance) delete from T_BuyDetails where UserID=@RelatedUserID and SchemeNumber=@schemenumber and ID=@BuyDetailID set @i=@i+1 if @i=@count begin return end end return end else --总进度不到90%,此单流产、 begin set @Demwer=1 --表示流产、进行撤单操作 --给发起人退款(此操作退的是方案表中的钱。没退跟单表中的钱) update T_Scheme set QuashStatus=@queshstate where SchemeNumber=@schemenumber update T_Users set Balance=Balance+@selfmoney+@diff,Freeze=Freeze-@selfmoney-@diff where UserID=@userid select @selfblance=Balance from T_Users insert into T_UserDealDetails(UserID,DealType,DetailMoney,SchemeNumber,RelatedUserID,NonceBalance) values(@userid,@dealtype,@selfmoney+@diff,@schemenumber,@userid,@selfblance) --给跟单人退款 set @count=0 select @count= COUNT(ID) from T_BuyDetails where SchemeNumber=@schemenumber print @count set @i=0 while(@count>@i) begin select @BuyDetailID=ID from T_BuyDetails where id = (select top 1 ID from T_BuyDetails where id not in (select top (@i) ID from T_BuyDetails where SchemeNumber=@schemenumber) and SchemeNumber=@schemenumber) select @RelatedUserID=UserID from T_BuyDetails where id = (select top 1 ID from T_BuyDetails where id not in (select top (@i) ID from T_BuyDetails where SchemeNumber=@schemenumber) and SchemeNumber=@schemenumber) select @withmoney=Price from T_BuyDetails where ID=@BuyDetailID update T_Users set Balance=Balance+@withmoney,Freeze=Freeze-@withmoney where UserID=@RelatedUserID set @selfblance=0 select @selfblance=Balance from T_Users where UserID=@RelatedUserID insert into T_UserDealDetails (UserID,DealType,DetailMoney,SchemeNumber,RelatedUserID,NonceBalance) values(@RelatedUserID,@dealtype,@withmoney,@schemenumber,@userid,@selfblance) delete from T_BuyDetails where UserID=@RelatedUserID and SchemeNumber=@schemenumber and ID=@BuyDetailID set @i=@i+1 if @i=@count begin return end end end end commit transaction end try begin catch --事物回滚 rollback transaction end catch select @Demwer as 'isbill' --是否可出票 end

zsstone的主页 zsstone | 初学一级 | 园豆:100
提问于:2011-08-12 11:05
< >
分享
所有回答(1)
0

 很明显是begin transaction与commit transaction 没有在同一层次(意思是commit transaction在begin transaction得begin -end语句 外)

 

你注释的地方要换行 不是拷贝出来 要自己去换 太多了

杯具程序员 | 园豆:1718 (小虾三级) | 2011-08-12 11:34
呃。。因为东西太多了。。复制上去就都换了行。变了的。。哈、嗯 。要是你愿意帮忙给我你QQ.然后把整齐的发你呵。
支持(0) 反对(0) zsstone | 园豆:100 (初学一级) | 2011-08-12 12:29
发到我的邮箱里 xiaowang505635531@163.com
支持(0) 反对(0) 杯具程序员 | 园豆:1718 (小虾三级) | 2011-08-12 13:06
你这个过程 commit 会提交2次
支持(0) 反对(0) 杯具程序员 | 园豆:1718 (小虾三级) | 2011-08-15 09:10
就是这个问题
而begin transaction只一个
支持(0) 反对(0) 杯具程序员 | 园豆:1718 (小虾三级) | 2011-08-15 09:11
嗯 。这其中begin transaction和commit transaction必须是成对出现的吗?
支持(0) 反对(0) zsstone | 园豆:100 (初学一级) | 2011-08-16 10:12
commit 可以有多个 但是你的代码 commit执行了2次
支持(0) 反对(0) 杯具程序员 | 园豆:1718 (小虾三级) | 2011-08-16 10:54
懂起意思没
支持(0) 反对(0) 杯具程序员 | 园豆:1718 (小虾三级) | 2011-08-16 10:55
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册