将 numeric 转换为数据类型 varchar 时出现算术溢出错误

2013-11-06 04:27:53 4.4k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

麒麟银河操作系统V10部署ffmpeg(也能用于Linux系统) 部署ffmpeg用来处理视频的各种操作，如编码解码，格式转换，剪辑，处理实时视频流，音频流，加滤镜等阅读详情

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 时失败。

...全文

python进行windows系统UI自动化之【pyautoit】一般以我们平时操作，会从下图上方的地址栏进行操作，这里注意可以直接从下方的文件名处，输入对应的绝对地址，如：F:\程序设计书籍\《啊哈！pyautoit 是一个用python写的基于AutoItX3.dll的接口库，用来进行windows窗口的一系列操作，也支持鼠标键盘的操作。点击上访的图标，进行拖拉，到想访问的位置，注意下图两个红框中的数值，上面的框表示页面的标题和类，下面的框表示控件的类和序号（一般连起来使用）打开Auto Window Info(x64)或Auto Window Info(x86) 阅读详情

sunping177

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

LongRui888 2013-11-07

打赏
举报

引用 11 楼 sunping177 的回复:

[quote=引用 8 楼 yupeigu 的回复:] [quote=引用 7 楼 sunping177 的回复:] [quote=引用 6 楼 yupeigu 的回复:] [quote=引用 4 楼 sunping177 的回复:] [quote=引用 2 楼 yupeigu 的回复:] 改成这样：
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

这个我试过啦，是错误的，报错为消息 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 --限制被除数不为零

[/quote] 试过啦，仍然报错消息 8115，级别 16，状态 5，过程 usp_OilStationSaleMonth，第 139 行将 numeric 转换为数据类型 varchar 时出现算术溢出错误。[/quote] 这次双管齐下，改成这样试试，加了一个cast转化，然后varchar(50)： substring(convert(varchar(50),cast((a.nMargin/a.nSaleAmount)*100 as numeric(8,2)),1,5)+'%' end[/quote] 这样存储过程是执行成功，但是还有一个错误消息 217，级别 16，状态 1，过程 usp_OilStationSaleMonth，第 218 行超出了存储过程、函数、触发器或视图的最大嵌套层数(最大层数为 32)。[/quote] 对了你写的是存储过程usp_OilStationSaleMonth把，是不是在里面usp_OilStationSaleMonth存储过程中，有这样的语句： exec usp_OilStationSaleMonth ……

sunping177 2013-11-07

打赏
举报

引用 8 楼 yupeigu 的回复:

[quote=引用 7 楼 sunping177 的回复:] [quote=引用 6 楼 yupeigu 的回复:] [quote=引用 4 楼 sunping177 的回复:] [quote=引用 2 楼 yupeigu 的回复:] 改成这样：


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


substring(convert (varchar(50),(a.nMargin/a.nSaleAmount)*100),1,5)+'%' end --限制被除数不为零

發糞塗牆 2013-11-07

打赏
举报

方法有两种： 1、在最后插入时才转换成字符串，带有百分比的那个，中间过程先不转换。 2、改表结构的字段类型，从numeric改成varchar

sunping177 2013-11-07

打赏
举报

引用 1 楼 DBA_Huangzj 的回复:

带有了%就变成了字符串了，你是要把字符串带进去吗？如果是，你的#ly的字段类型不能为numeric，否则，你的#lyg插入时就不要带上%

因为要求百分比，所以%是要带进去的，#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

打赏
举报

引用 7 楼 sunping177 的回复:

[quote=引用 6 楼 yupeigu 的回复:] [quote=引用 4 楼 sunping177 的回复:] [quote=引用 2 楼 yupeigu 的回复:] 改成这样：


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


substring(convert (varchar(50),(a.nMargin/a.nSaleAmount)*100),1,5)+'%' end --限制被除数不为零

sunping177 2013-11-07

打赏
举报

引用 6 楼 yupeigu 的回复:

[quote=引用 4 楼 sunping177 的回复:] [quote=引用 2 楼 yupeigu 的回复:] 改成这样：


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


substring(convert (varchar(50),(a.nMargin/a.nSaleAmount)*100),1,5)+'%' end --限制被除数不为零

[/quote] 试过啦，仍然报错消息 8115，级别 16，状态 5，过程 usp_OilStationSaleMonth，第 139 行将 numeric 转换为数据类型 varchar 时出现算术溢出错误。

LongRui888 2013-11-07

打赏
举报

引用 4 楼 sunping177 的回复:

[quote=引用 2 楼 yupeigu 的回复:] 改成这样：


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


substring(convert (varchar(50),(a.nMargin/a.nSaleAmount)*100),1,5)+'%' end --限制被除数不为零

發糞塗牆 2013-11-07

打赏
举报

我的问题呢？

sunping177 2013-11-07

打赏
举报

引用 2 楼 yupeigu 的回复:

改成这样：


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

这个我试过啦，是错误的，报错为消息 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

打赏
举报

引用 5 楼 DBA_Huangzj 的回复:

我的问题呢？

DBA_Huangzj 版主您受委屈啦，结合您二位的建议，我终于解决啦问题，非常感谢。

sunping177 2013-11-07

打赏
举报

引用 13 楼 sunping177 的回复:

[quote=引用 3 楼 yupeigu 的回复:] 之所以会有这样的问题是，case when中如果nSaleAmount = 0那么就返回0，这个0是int，也就是整数，而else后面的加上了'%'就变成了字符串了，而默认情况下sql server会把字符串，转为int，而%又不能转化为int，所以才报错的。上面的代码把0改为了'0'，也就是改成了字符串就好了

在存储过程中是有这样的语句： exec usp_OilStationSaleMonth '2012-09-01','',''[/quote] 非常感谢您的回复，我的问题现在解决啦在您说的这些的基础上，结合楼上的说法，中间过程先不转换，在最后插入时再将带有百分比的转换成字符串。

LongRui888 2013-11-07

打赏
举报

引用 13 楼 sunping177 的回复:

[quote=引用 3 楼 yupeigu 的回复:] 之所以会有这样的问题是，case when中如果nSaleAmount = 0那么就返回0，这个0是int，也就是整数，而else后面的加上了'%'就变成了字符串了，而默认情况下sql server会把字符串，转为int，而%又不能转化为int，所以才报错的。上面的代码把0改为了'0'，也就是改成了字符串就好了

在存储过程中是有这样的语句： exec usp_OilStationSaleMonth '2012-09-01','',''[/quote] 这样就导致了，递归调用，也就是存储过程usp_OilStationSaleMonth 自己调用自己，一旦超过了32次，就汇报错了，必须得去掉这样的语句。

sunping177 2013-11-07

打赏
举报

引用 3 楼 yupeigu 的回复:

之所以会有这样的问题是，case when中如果nSaleAmount = 0那么就返回0，这个0是int，也就是整数，而else后面的加上了'%'就变成了字符串了，而默认情况下sql server会把字符串，转为int，而%又不能转化为int，所以才报错的。上面的代码把0改为了'0'，也就是改成了字符串就好了

在存储过程中是有这样的语句： 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插入时就不要带上%

将 numeric 转换为数据类型 varchar 时出现算术溢出错误

相关推荐