Link to home
Start Free TrialLog in
Avatar of countrymeister
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(isnull(@TotalValue, @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(isnull(@TotalCash, @TotalValue) * (case when @CurrencyID != 'USD' and @ConversionRate != 0 then 1 / @ConversionRate else 1 end),2)))  
PRINT @USDEqu
ASKER CERTIFIED SOLUTION
Avatar of Rich Weissler
Rich Weissler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial