存储过程问题,没有分了.不图分的请进,先谢谢

Question

--自营盘口的开盘
CREATE PROCEDURE BetAdmin_HandicapAct
@Act varchar(20),
@MatchID int,
@Oddsstr varchar(1000),@fOddsstr varchar(1000),@HandicapType int,
@Eventtypestr varchar(1000),
@BatchNo int,
@result int output
/*
0--成功
1--该比赛还没有该玩法
2--赔率不能为空或小于等于1
3--自营盘口对于此种盘口类型不能开多盘
4--自营盘口开多盘时盘口不能重复
5--只有未审核或被驳回的初盘才能修改
6--只有审核通过的盘口才能更新赔率7--盘口类型错误
8--盘口信息错误，自营盘之间有交易可能
*/
ASset @result =0declare @Odds float
declare @fOdds float
declare @Eventtype int
declare @Reverse smallint
declare @OpenMoney float
declare @i_odds int
declare @k_odds intdeclare @i_fodds int
declare @k_fodds intdeclare @Openid int
declare @BatchNo_Bargain int
declare @HandicapID int
declare @res int--先判断可行性
select @HandicapID=HandicapID from Handicap_Match where matchid=@matchid and HandicapType=@HandicapType
if @@rowcount=0
BEGIN
set @result=1
return
ENDif @ACT<>'modify'
BEGIN
set @k_odds=0
set @i_odds=LEN(@Oddsstr)/20        set @k_fodds=0
set @i_fodds=LEN(@fOddsstr)/20 while @k_odds<@i_odds
BEGIN
set @Odds=RTRIM(LTRIM(SUBSTRING(@Oddsstr,@k_odds*20+1,20)))
if @Odds='' or @odds<=1
BEGIN
set @result=2
return
END
set @k_odds=@k_odds+1
END       while @k_fodds<@i_fodds
BEGIN
set @fOdds=RTRIM(LTRIM(SUBSTRING(@fOddsstr,@k_fodds*20+1,20)))
if @fOdds='' 
BEGIN
set @result=2
return
END
set @k_fodds=@k_fodds+1
END
ENDif @ACT='add'
BEGIN
if @handicapType=1 or @handicapType=2 or @handicapType=7 or @handicapType=10 or @handicapType=13 or @handicapType=14 or @handicapType=16 or @handicapType=19 or @handicapType=20
BEGIN
select @Openid=Openid from Handicap_Open where OpenType=1 and HandicapID=@HandicapID
if @@rowcount<>0
BEGIN
set @result=3
return
END
END
else
BEGIN
select @Openid=Openid from Handicap_Open where OpenType=1 and HandicapID=@HandicapID and abs(Eventtype)=abs(RTRIM(LTRIM(left(@Eventtypestr,20))))
if @@rowcount<>0
BEGIN
set @result=4
return
END
END
END
else if @ACT='modify_odds'
BEGIN
select @Openid=Openid from Handicap_Open where OpenType=1 and BatchNo=@BatchNo and flag<>0 and flag<>2
if @@rowcount<>0
BEGIN
set @result=5
return
END
if @handicapType=0 or @handicapType=3 or @handicapType=6 or @handicapType=8 or @handicapType=9 or @handicapType=11 or @handicapType=12 or @handicapType=15 or @handicapType=17 or @handicapType=18
BEGIN
select @Openid=Openid from Handicap_Open where OpenType=1 and HandicapID=@HandicapID and abs(Eventtype)=abs(RTRIM(LTRIM(left(@Eventtypestr,20)))) and BatchNo<>@BatchNo
if @@rowcount<>0
BEGIN
set @result=4
return
END
END
END
else IF @ACT='update'
BEGIN
select @Openid=Openid from Handicap_Open where OpenType=1 and BatchNo=@BatchNo and flag<>1
if @@rowcount<>0
BEGIN
set @result=6
return
END
if @handicapType=0 or @handicapType=3 or @handicapType=6 or @handicapType=8 or @handicapType=9 or @handicapType=11 or @handicapType=12 or @handicapType=15 or @handicapType=17 or @handicapType=18
BEGIN
select @Openid=Openid from Handicap_Open where OpenType=1 and HandicapID=@HandicapID and abs(Eventtype)=abs(RTRIM(LTRIM(left(@Eventtypestr,20)))) and BatchNo<>@BatchNo
if @@rowcount<>0
BEGIN
set @result=4
return
END
END
END
修改加入一参数后,有错误,错误提示:[Microsoft][ODBC SQL Server Driver][SQL Server]将数据类型 varchar 转换为 int 时出错。
如何调啊?

Answer 1

SET XACT_ABORT ON
BEGIN TRANSACTIONif @ACT='add'
BEGIN
set @k_odds=0
set @i_odds=LEN(@Oddsstr)/20        set @k_fodds=0
set @i_fodds=LEN(@fOddsstr)/20 while @k_odds<@i_odds
BEGIN
set @Odds=RTRIM(LTRIM(SUBSTRING(@Oddsstr,@k_odds*20+1,20)))                set @fOdds=RTRIM(LTRIM(SUBSTRING(@fOddsstr,@k_fodds*20+1,20))) set @Eventtype=RTRIM(LTRIM(SUBSTRING(@Eventtypestr,@k_odds*20+1,20)))
set @Odds=@Odds-1
set @OpenMoney=20000
set @Reverse=1 if @eventtype<0
BEGIN
SET @eventtype=-@eventtype
set @OpenMoney=-@openMoney*@odds
set @odds=1/@odds
set @Reverse=-1
END

if @k_odds=0
BEGIN
insert into Handicap_Open(HandicapID,EventType,Odds,OpenMoney,DepositID,OpenType,BatchNo,Reverse,fodds) values(@HandicapID,@EventType,@Odds,@OpenMoney,0,1,-1,@Reverse,@fOdds)  --********lanweigai
set @Openid=@@IDENTITY
set @BatchNo=@OpenID
update Handicap_Open set BatchNo=@BatchNo where OpenID=@Openid
insert into Handicap_Open_Bargain(RelativeBargainID,HandicapID,EventType,Odds,OpenMoney,DepositID,OpenType,BatchNo,Reverse) values(@Openid,@HandicapID,@EventType,@Odds,0,0,1,-1,@Reverse)
set @BatchNo_Bargain=@@IDENTITY
update Handicap_Open_Bargain set BatchNo=@BatchNo_Bargain where BargainID=@BatchNo_Bargain
END
else
BEGIN
insert into Handicap_Open(HandicapID,EventType,Odds,OpenMoney,DepositID,OpenType,BatchNo,Reverse,fodds) values(@HandicapID,@EventType,@Odds,@OpenMoney,0,1,@BatchNo,@Reverse,@fOdds)
set @Openid=@@IDENTITY
insert into Handicap_Open_Bargain(RelativeBargainID,HandicapID,EventType,Odds,OpenMoney,DepositID,OpenType,BatchNo,Reverse) values(@Openid,@HandicapID,@EventType,@Odds,0,0,1,@BatchNo_Bargain,@Reverse)
END
set @k_odds=@k_odds+1
ENDEND
else if @ACT='modify_odds'
BEGIN
set @k_odds=0
set @i_odds=LEN(@Oddsstr)/20        set @k_fodds=0
set @i_fodds=LEN(@fOddsstr)/20 while @k_odds<@i_odds
BEGIN
set @Odds=RTRIM(LTRIM(SUBSTRING(@Oddsstr,@k_odds*20+1,20)))                set @fOdds=RTRIM(LTRIM(SUBSTRING(@fOddsstr,@k_fodds*20+1,20))) set @Eventtype=RTRIM(LTRIM(SUBSTRING(@Eventtypestr,@k_odds*20+1,20)))
set @Odds=@Odds-1
set @OpenMoney=20000
set @Reverse=1 if @eventtype<0
BEGIN
SET @eventtype=-@eventtype
set @OpenMoney=-@openMoney*@odds
set @odds=1/@odds
set @Reverse=-1
END if @handicapType=1 or @handicapType=2 or @handicapType=10 or @handicapType=13 or @handicapType=14
select @Openid=Openid from Handicap_Open where BatchNo=@BatchNo and OpenType=1 and eventtype=@eventtype
else if @handicapType=15
select @Openid=Openid from Handicap_Open where BatchNo=@BatchNo and OpenType=1 and right(eventtype,1)=right(@eventtype,1)
else
select @Openid=Openid from Handicap_Open where BatchNo=@BatchNo and OpenType=1 and Reverse=@Reverse
update Handicap_Open set EventType=@EventType,OpenMoney=@OpenMoney,Odds=@Odds,flag=0 where Openid=@Openid
--盘口还未审核，Handicap_Open_Bargain表中没有该盘口的其他交易记录
update Handicap_Open_Bargain set EventType=@EventType,Odds=@Odds where RelativeBargainID=@Openid and Opentype=1 set @k_odds=@k_odds+1
END
END
else if @ACT='update'
BEGIN
set @k_odds=0
set @i_odds=LEN(@Oddsstr)/20        set @k_fodds=0
set @i_fodds=LEN(@fOddsstr)/20 while @k_odds<@i_odds
BEGIN
set @Odds=RTRIM(LTRIM(SUBSTRING(@Oddsstr,@k_odds*20+1,20)))                set @fOdds=RTRIM(LTRIM(SUBSTRING(@fOddsstr,@k_fodds*20+1,20)))             set @Eventtype=RTRIM(LTRIM(SUBSTRING(@Eventtypestr,@k_odds*20+1,20)))
set @Odds=@Odds-1
set @OpenMoney=20000
set @Reverse=1 if @eventtype<0
BEGIN
SET @eventtype=-@eventtype
set @OpenMoney=-@openMoney*@odds
set @odds=1/@odds
set @Reverse=-1
END if @handicapType=1 or @handicapType=2 or @handicapType=10 or @handicapType=13 or @handicapType=14
select @Openid=Openid from Handicap_Open where BatchNo=@BatchNo and OpenType=1 and eventtype=@eventtype
else if @handicapType=15
select @Openid=Openid from Handicap_Open where BatchNo=@BatchNo and OpenType=1 and right(eventtype,1)=right(@eventtype,1)
else
select @Openid=Openid from Handicap_Open where BatchNo=@BatchNo and OpenType=1 and Reverse=@Reverse
update Handicap_Open set EventType=@EventType,OpenMoney=@OpenMoney,Odds=@Odds where Openid=@Openid
update Handicap_Open_Bargain set RelativeBargainID=0 where RelativeBargainID=@Openid and OpenType=1

if @k_odds=0
BEGIN
insert into Handicap_Open_Bargain(RelativeBargainID,HandicapID,EventType,Odds,OpenMoney,DepositID,OpenType,BatchNo,Reverse) values(@Openid,@HandicapID,@EventType,@Odds,0,0,1,-1,@Reverse)
set @BatchNo_Bargain=@@IDENTITY
update Handicap_Open_Bargain set BatchNo=@BatchNo_Bargain where BargainID=@BatchNo_Bargain
END
else
BEGIN
insert into Handicap_Open_Bargain(RelativeBargainID,HandicapID,EventType,Odds,OpenMoney,DepositID,OpenType,BatchNo,Reverse) values(@Openid,@HandicapID,@EventType,@Odds,0,0,1,@BatchNo_Bargain,@Reverse)
END
exec BetAdmin_Bourse_SSAutoTrade @Openid, @res output
if @res=1
BEGIN
set @result=7
rollback TRANSACTION
return
END
if @res=2
BEGIN
set @result=8
rollback TRANSACTION
return
END
set @k_odds=@k_odds+1
END
END
else if @ACT='undo'
BEGIN
update handicap_open set undoflag=undoflag^1 where BatchNo=@BatchNo
END
COMMIT TRANSACTION
GO