在存储过程(参数化)计算中将varchar转换为数据类型numeric时发生算术溢出错误

Question

我想计算包括vat、shippingcost、total的grandtoal，同时使用@total计算total，获取将varchar转换为数据类型数字错误的算术溢出错误。如何解决此

PROCEDURE   [dbo].[GrandTotal] 
@GrandTotal decimal(10,2)

as
BEGIN
DECLARE @1stQuery AS NVARCHAR(4000)
DECLARE @ParamDefinition AS NVARCHAR(2000)
DECLARE @Total AS decimal (18,12)
DECLARE @vat AS decimal (10,2)
DECLARE @Gtotal AS decimal (10,2)

set @Total =  'select ((L.Quantity*L.Price)- L.DiscountValue-((O.AllocatedPoint / 100) * O.ExchangeRate))AS Total 
FROM [Orders] O 
LEFT JOIN [LineItem] L  ON O.[OrderId]  = L.[OrderId]  where L.[StatusCode] IN (''AS'', ''PP'',  ''P'',''PS'',''O'',''OH'',''SC'',''D'', ''GA'',''SW'')'

set @vat = @Total / (100 + 20) * 20;
set @Gtotal =@Total-@vat
set  @1stQuery =  
' select   orderid,GrandTotal from (SELECT  O.[OrderId],
CAST( ROUND((@Gtotal+O.ShippingCost),2)AS DECIMAL(10,2)) AS GrandTotal
FROM    LineItem L 
left JOIN [Orders] O  ON O.[OrderId]  = L.[OrderId]
left join [Country] C on O.[CountryId] = c.[CountryId]
)as gt where 1=1 
'

if @GrandTotal  is not null 
SET @1stQuery = @1stQuery + 'and  gt.[GrandTotal] = @GrandTotal   ' 

set @ParamDefinition='@GrandTotal decimal (10,2),@Total decimal(18,12),@Gtotal decimal(10,2),@vat decimal(10,2)
'
EXECUTE sp_Executesql @1stQuery,
@ParamDefinition,
@GrandTotal,
@Total,
@Gtotal,@vat

END

Answer 1

您的错误与计算无关，这是因为您已将@total声明为十进制，然后为其分配了一个字符串值。您只需使用即可重新创建错误

DECLARE @Total AS decimal (18,12)
set @Total =  'select ((L.Quantity*L.Price)- L.DiscountValue-((O.AllocatedPoint / 100) * O.ExchangeRate))AS Total 
FROM [Orders] O 
LEFT JOIN [LineItem] L  ON O.[OrderId]  = L.[OrderId]  where L.[StatusCode] IN (''AS'', ''PP'',  ''P'',''PS'',''O'',''OH'',''SC'',''D'', ''GA'',''SW'')'

产生

Msg8115，级别16，状态6，第3行

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

值得一提的是，您的查询或操作中还有无数其他错误，我看不出背后的逻辑，但根本不清楚您想要实现什么，所以我还不能提出替代方案。不过我要指出的是，根本不需要动态SQL！