34,594
社区成员
发帖
与我相关
我的任务
分享将 numeric 转换为数据类型 varchar 时出现算术溢出错误
sqlserver2005存储过程中限制被除数不为0，局部代码如下
第 139 行
insert into #lyg
select b.sStationNO,
case when nSaleAmount=0 then 0 else substring(convert (varchar(20),(a.nMargin/a.nSaleAmount)*100),1,5)+'%' end --限制被除数不为零
from tSaleDaily a,#ly b where a.sStationNO=b.sStationNO and a.dReportDate between dateadd(dd,-392,@BeginDate) and dateadd(dd,-364,@BeginDate)
group by b.sStationNO,a.nMargin,a.nSaleAmount
update #ly set nThisMonthMaoLiLv=a.nMaoLiLv from #lyh a,#ly b where a.sStationNO=b.sStationNO
但是执行存储过程的时候总是报错，错误如下
过程 usp_OilStationSaleMonth，第 139 行 在将 varchar 值 '21.42%' 转换成数据类型 int 时失败。
第 139 行
insert into #lyg
select b.sStationNO,
case when nSaleAmount=0 then 0 else substring(convert (varchar(20),(a.nMargin/a.nSaleAmount)*100),1,5)+'%' end --限制被除数不为零
from tSaleDaily a,#ly b where a.sStationNO=b.sStationNO and a.dReportDate between dateadd(dd,-392,@BeginDate) and dateadd(dd,-364,@BeginDate)
group by b.sStationNO,a.nMargin,a.nSaleAmount
update #ly set nThisMonthMaoLiLv=a.nMaoLiLv from #lyh a,#ly b where a.sStationNO=b.sStationNO
但是执行存储过程的时候总是报错，错误如下
过程 usp_OilStationSaleMonth，第 139 行 在将 varchar 值 '21.42%' 转换成数据类型 int 时失败。
...全文
请发表友善的回复…
发表回复
LongRui888 2013-11-07
- 打赏
- 举报
这个我试过啦，是错误的，报错为
消息 8115，级别 16，状态 5，过程 usp_OilStationSaleMonth，第 139 行
将 numeric 转换为数据类型 varchar 时出现算术溢出错误。[/quote]
我知道了，改为这样试试，把varchar(20)改为varchar(50)：
substring(convert (varchar(50),(a.nMargin/a.nSaleAmount)*100),1,5)+'%' end --限制被除数不为零
sunping177 2013-11-07
- 打赏
- 举报
这个我试过啦，是错误的，报错为
消息 8115，级别 16，状态 5，过程 usp_OilStationSaleMonth，第 139 行
将 numeric 转换为数据类型 varchar 时出现算术溢出错误。[/quote]
我知道了，改为这样试试，把varchar(20)改为varchar(50)：
substring(convert (varchar(50),(a.nMargin/a.nSaleAmount)*100),1,5)+'%' end --限制被除数不为零
發糞塗牆 2013-11-07
- 打赏
- 举报
方法有两种：
1、在最后插入时才转换成字符串，带有百分比的那个，中间过程先不转换。
2、改表结构的字段类型，从numeric改成varchar
sunping177 2013-11-07
- 打赏
- 举报
因为要求百分比，所以%是要带进去的，#ly是有多个字段类型的临时表，如何让#ly的字段类型不为numeric
create table #ly
(sStationNO varchar(10),
sStationName varchar(100),
nThisMonthSale decimal(20,4),--本月销售
nLastMonthSale decimal(20,4), --上月同期销售
nLastMonthSaleUpOrDown varchar(20),--上月同期销售升降比
nLastYearSale decimal(20,4),--去年同期销售
nLastYearSaleUpOrDown varchar(20),--去年同期销售升降比
nThisMonthGroupSale decimal(20,4),--本月团购销售
nLastMonthGroupSale decimal(20,4),--上月同期团购销售
nLastYearGoupSale decimal(20,4),--去年同期团购销售
nThisMonthKeLiu int,--本月客流
nLastMonthKeLiu int,--上月同期客流
nLastYearKeLiu int,--去年同期客流
nThisMonthMaoLiLv varchar(20),--本月毛利率
nLastMonthMaoLiLv varchar(20),--上月同期毛利率
nLastYearMaoLiLv varchar(20))--去年同期毛利率
insert into #ly
select a.sStationNO,a.sStationName,0,0,
case when nLastMonthSale=0 then '0' else substring(convert (varchar(50),((a.nThisMonthSale-a.nLastMonthSale)/a.nLastMonthSale)*100),1,5)+'%' end,0,0,0,0,0,0,0,0,0,0,0
from #ly a
Group by a.sStationNO,a.sStationName,a.nThisMonthSale,a.nLastMonthSale
LongRui888 2013-11-07
- 打赏
- 举报
这个我试过啦，是错误的，报错为
消息 8115，级别 16，状态 5，过程 usp_OilStationSaleMonth，第 139 行
将 numeric 转换为数据类型 varchar 时出现算术溢出错误。[/quote]
我知道了，改为这样试试，把varchar(20)改为varchar(50)：
substring(convert (varchar(50),(a.nMargin/a.nSaleAmount)*100),1,5)+'%' end --限制被除数不为零
sunping177 2013-11-07
- 打赏
- 举报
这个我试过啦，是错误的，报错为
消息 8115，级别 16，状态 5，过程 usp_OilStationSaleMonth，第 139 行
将 numeric 转换为数据类型 varchar 时出现算术溢出错误。[/quote]
我知道了，改为这样试试，把varchar(20)改为varchar(50)：
substring(convert (varchar(50),(a.nMargin/a.nSaleAmount)*100),1,5)+'%' end --限制被除数不为零
LongRui888 2013-11-07
- 打赏
- 举报
这个我试过啦，是错误的，报错为
消息 8115，级别 16，状态 5，过程 usp_OilStationSaleMonth，第 139 行
将 numeric 转换为数据类型 varchar 时出现算术溢出错误。[/quote]
我知道了，改为这样试试，把varchar(20)改为varchar(50)：
substring(convert (varchar(50),(a.nMargin/a.nSaleAmount)*100),1,5)+'%' end --限制被除数不为零
發糞塗牆 2013-11-07
- 打赏
- 举报
我的问题呢？sunping177 2013-11-07
- 打赏
- 举报
这个我试过啦，是错误的，报错为
消息 8115，级别 16，状态 5，过程 usp_OilStationSaleMonth，第 139 行
将 numeric 转换为数据类型 varchar 时出现算术溢出错误。
中国风 2013-11-07
- 打赏
- 举报
'21.42%'--有'%'符号不能转换
case when nSaleAmount=0 then 0 else substring(convert (varchar(20),(a.nMargin/a.nSaleAmount)*100),1,5)+'%' end CASE WHEN nLastMonthSale = 0 THEN '0'
ELSE STR( ( a.nThisMonthSale - a.nLastMonthSale )/ a.nLastMonthSale * 100,5
2) + '%'
END發糞塗牆 2013-11-07
- 打赏
- 举报
sunping177 2013-11-07
- 打赏
- 举报
DBA_Huangzj 版主您受委屈啦，结合您二位的建议，我终于解决啦问题，非常感谢。
sunping177 2013-11-07
- 打赏
- 举报
在存储过程中是有这样的语句：
exec usp_OilStationSaleMonth '2012-09-01','',''[/quote]
非常感谢您的回复，我的问题现在解决啦
在您说的这些的基础上，结合楼上的说法，中间过程先不转换，在最后插入时再将带有百分比的转换成字符串。
LongRui888 2013-11-07
- 打赏
- 举报
在存储过程中是有这样的语句：
exec usp_OilStationSaleMonth '2012-09-01','',''[/quote]
这样就导致了，递归调用，也就是存储过程usp_OilStationSaleMonth 自己调用自己，一旦超过了32次，就汇报错了，必须得去掉这样的语句。
sunping177 2013-11-07
- 打赏
- 举报
在存储过程中是有这样的语句：
exec usp_OilStationSaleMonth '2012-09-01','',''
LongRui888 2013-11-06
- 打赏
- 举报
之所以会有这样的问题是，case when中如果nSaleAmount = 0那么就返回0，这个0是int，也就是整数，
而else后面的加上了'%'就变成了字符串了，而默认情况下sql server会把字符串，转为int，而%又不能转化为int，所以才报错的。
上面的代码把0改为了'0'，也就是改成了字符串 就好了
LongRui888 2013-11-06
- 打赏
- 举报
改成这样：
insert into #lyg
select b.sStationNO,
case when nSaleAmount=0 then '0' else substring(convert (varchar(20),(a.nMargin/a.nSaleAmount)*100),1,5)+'%' end --限制被除数不为零
from tSaleDaily a,#ly b where a.sStationNO=b.sStationNO and a.dReportDate between dateadd(dd,-392,@BeginDate) and dateadd(dd,-364,@BeginDate)
group by b.sStationNO,a.nMargin,a.nSaleAmount
update #ly set nThisMonthMaoLiLv=a.nMaoLiLv from #lyh a,#ly b where a.sStationNO=b.sStationNO
發糞塗牆 2013-11-06
- 打赏
- 举报
带有了%就变成了字符串了，你是要把字符串带进去吗？如果是，你的#ly的字段类型不能为numeric，否则，你的#lyg插入时就不要带上%
