How implicit varchar to numeric works till 999 and does not work after that

Question

The below sql works:

DECLARE @VAL1 VARCHAR(50), @VAL2 INT
SET @VAL1 = '999'
SET @VAL2 = 6414
SELECT ROUND(CAST(((@VAL1 * 100.00)/@VAL2) AS FLOAT), 2)

Where as this one fails with 'Arithmetic overflow error converting varchar to data type numeric.'

DECLARE @VAL1 VARCHAR(50), @VAL2 INT
SET @VAL1 = '1000'
SET @VAL2 = 6414
SELECT ROUND(CAST(((@VAL1 * 100.00)/@VAL2) AS FLOAT), 2)

The only difference between the two is that in the second one @VAL1 value is 1000 instead of 999.

Answer 1

You are implicitly converting the string into a number by multtiplying it with a number. The type of the number that you are multiplying with determines the type that the string is converted to. The database uses the smallest numeric type that can hold the number 100.00 which is numeric(5,2), so that's what the string is converted to too.

If you explicitly convert the string to a number, you can choose a type that can handle the range that you need.

If you for example convert it to an int it can handle values up to '2147483647':

SELECT ROUND(CAST(((CAST(@VAL1 AS INT) * 100.00)/@VAL2) AS FLOAT), 2)