SudeepShakya
SudeepShakya

Reputation: 621

Arithmetic overflow error converting expression to data type money

I have following query, where datatype of accc_no is varchar, wthdrl_amt is money, sav_amt is money, trxn_id is bigint.

DECLARE @acc_no NVARCHAR(MAX) = N''
DECLARE @symbol NVARCHAR(MAX) = N''
DECLARE @loop int = 0
DECLARE @loop2 int = 0

SELECT @symbol += N'' + acc_no + ',' FROM sav_transaction GROUP BY acc_no HAVING (SUM(sav_amt)<SUM(wthdrl_amt))
IF LEN(@symbol) > 1
BEGIN
    WHILE @loop < LEN(@symbol)
    BEGIN
        SET @loop2 = CHARINDEX(',', @symbol ,@loop)
        SET @acc_no = SUBSTRING(@symbol, @loop, @loop2-@loop)

        UPDATE sav_transaction
        SET wthdrl_amt = wthdrl_amt-(SELECT ABS(SUM(sav_amt)  - SUM(wthdrl_amt))
        FROM sav_transaction WHERE acc_no = @acc_no)
        WHERE acc_no = @acc_no AND trxn_id = (SELECT MAX(trxn_id) FROM sav_transaction WHERE acc_no = @acc_no )
    END
    SET @loop = @loop2 +1
END

But when I run this, I get error :

Msg 8115, Level 16, State 2, Line 15
Arithmetic overflow error converting expression to data type money.
The statement has been terminated.

I have used the field with same data type. But couldn't find the error.

Upvotes: 0

Views: 25168

Answers (2)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

To see the differences we can look at the documentation:

Documentation for money:

Data type  Range                                                 Storage
money      -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes
smallmoney -214,748.3648 to 214,748.3647                         4 bytes

The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent.

Compare to decimal:

When maximum precision is used, valid values are from -10^38 + 1 through 10^38 - 1.

Precision    Storage
1 - 9        5 bytes
10 - 19      9 bytes
20 - 28      13 bytes
29 - 38      17 bytes

So they're not exactly equivalent, just similar. A DECIMAL(19,4) has a slightly greater range than MONEY (it can store from -10^15 + 0.0001 to 10^15 - 0.0001), but also needs one more byte of storage.

In other words, this works:

CREATE TABLE Table1 (test DECIMAL(19,4) NOT NULL);
INSERT INTO Table1 (test) VALUES
(999999999999999.9999);
SELECT * FROM Table1 

999999999999999.9999

But this doesn't:

CREATE TABLE Table1 (test MONEY NOT NULL);
INSERT INTO Table1 (test) VALUES
(999999999999999.9999);
SELECT * FROM Table1 

Arithmetic overflow error converting numeric to data type money.

There's also a semantic difference. If you want to store monetary values, it makes sense to use the type money.

Source

Upvotes: 2

DhruvJoshi
DhruvJoshi

Reputation: 17136

Changed this part SELECT @symbol += N'' + CAST(acc_no AS nvarchar(MAX)) + as acc_no is not nvarchar and takes precedence during add operation and tries to cast N'' and other additions to its type

DECLARE @acc_no NVARCHAR(MAX) = N''
    DECLARE @symbol NVARCHAR(MAX) = N''
    DECLARE @loop int = 0
    DECLARE @loop2 int = 0

    SELECT @symbol += N'' + CAST(acc_no AS nvarchar(MAX)) + ',' FROM sav_transaction GROUP BY acc_no HAVING (SUM(sav_amt)<SUM(wthdrl_amt))
    IF LEN(@symbol) > 1
    BEGIN
    WHILE @loop < LEN(@symbol)
    BEGIN

                SET @loop2 = CHARINDEX(',', @symbol ,@loop)
                SET @acc_no = SUBSTRING(@symbol, @loop, @loop2-@loop)

    UPDATE sav_transaction SET 
    wthdrl_amt = wthdrl_amt-(SELECT SUM(sav_amt)  - SUM(wthdrl_amt)  FROM sav_transaction WHERE acc_no = @acc_no)
    WHERE acc_no = @acc_no AND trxn_id = (SELECT MAX(trxn_id) FROM sav_transaction WHERE acc_no = @acc_no )


    END
    SET @loop = @loop2 +1
    END

Upvotes: 0

Related Questions