TSQL String Concatenation Arithmetic overflow error

Question

I'm using sql server 2008 r2. When I run my query, I receive "Arithmetic overflow error converting expression to data type int." I suspect the issue is in the part that begins with the RIGHT function. Here is the entire query.

SELECT
    'AA' + --BANK NUMBER
    TDAccountNumber + --ACCOUNT NUMBER
    '         ' + --SECURITY NUMBER
    '      ' + --POSTING DATE
    RIGHT('0000000000000' + CONVERT(VARCHAR(13),CONVERT(INT,ABS(A.PRINCIPALCASHBALANCE * 100))),13) + --INCOME CASH AMOUNT
    '0000000000000' + --PRINCIPAL CASH AMOUNT
    '640' + --TAX CODE
    '  ' + --REG CODE
    ' ' + --STATEMENT PRINT CODE
    '                   ' + --SPACES
    CASE
        WHEN A.PRINCIPALCASHBALANCE > 0 THEN 'T4@'
        ELSE 'T3@'
    END + --TRAN CODE
    CHAR(13) + CHAR(10) + --NEW LINE
    'AA' + --BANK NUMBER
    TDAccountNumber + --ACCOUNT NUMBER
    '         ' + --SECURITY NUMBER
    'TRANSFER FROM PRINCIPAL TO INCOME   ' + --DESCRIPTION
    '                     ' + --SPACES
    CASE
        WHEN A.PRINCIPALCASHBALANCE > 0 THEN 'T4B'
        ELSE 'T3B'
    END --TRAN CODE

    FROM
        TableName

The strange part is that if I comment out all parts of my select clause except the line that begins with the RIGHT function, the results are as I expect. If I comment out just that line and select the rest, the results are as I expect. It's doing something I don't understand quite yet when I attempt to select it all together. Thanks in advance for any help understanding a way around this problem.

Answer 1

Your value for A.PRINCIPALCASHBALANCE (when multipled by 100) is simply overflowing the maximium size of an int.

As the docs state, the maximum value an int can hold is 2,147,483,647. So if we break what you have down and just do:

select CONVERT(INT,ABS(21474836 * 100))

Then it works fine. However, if we change that so it will overflow:

select CONVERT(INT,ABS(21474837 * 100))

Then we get the error

Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int.

The same would happen if you are outside the lower bound of an int too.