countrymeister
asked on
SQL - Arithmetic overflow error for type money
The below script has the arithmetic overflow error, when the @TotalCash is null.
How could I avoid this error
DECLARE
@ConversionRate decimal(18,6)
, @TotalValue float
, @USDEqu numeric(28,2)
, @TotalCash money
, @CurrencyID varchar(10)
SET @TotalValue = 5.05835465000737E+17
SET @ConversionRate = 1.000000
SET @CurrencyID = 'USD'
-- This works
SET @USDEqu = abs(convert(numeric(28,2), round(isnu ll(@TotalV alue, @TotalCash) * (case when @CurrencyID != 'USD' and @ConversionRate != 0 then 1 / @ConversionRate else 1 end),2)))
PRINT @USDEqu
-- This fails
SET @USDEqu = abs(convert(numeric(28,2), round(isnu ll(@TotalC ash, @TotalValue) * (case when @CurrencyID != 'USD' and @ConversionRate != 0 then 1 / @ConversionRate else 1 end),2)))
PRINT @USDEqu
How could I avoid this error
DECLARE
@ConversionRate decimal(18,6)
, @TotalValue float
, @USDEqu numeric(28,2)
, @TotalCash money
, @CurrencyID varchar(10)
SET @TotalValue = 5.05835465000737E+17
SET @ConversionRate = 1.000000
SET @CurrencyID = 'USD'
-- This works
SET @USDEqu = abs(convert(numeric(28,2),
PRINT @USDEqu
-- This fails
SET @USDEqu = abs(convert(numeric(28,2),
PRINT @USDEqu
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.