Reputation: 621
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
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.
Upvotes: 2
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