using decimal in where clause - Arithmetic overflow error converting nvarchar to data type numeric

Question

I got a sql server error and not sure how to fix it.I got a column 'NAME' in a view 'Products' with a type of nvarchar(30), the query is generated dynamically in code so cannot quite change it.

I got the 'Arithmetic overflow error converting nvarchar to data type numeric.' for the following query:

select * FROM Products WHERE  NAME=12.0

however the following query works fine:

select * FROM Products WHERE  NAME=112.0

I am quite confused by the error, I know I should put quotes around the number but just want know why the second query works and is there any settings could make the first query work?

update: also

select * FROM Products WHERE  NAME=cast('12.0' as decimal(4,2))

doesn't work, but

select * FROM Products WHERE  NAME=cast('12.0' as decimal(5,2)) 

works, any particular reasons?

Many thanks!

Answer 1

SQL Server is trying to convert the values in your table to match the perceived data type of the value coded into your WHERE clause. If you have data values with more numbers (e.g., DECIMAL(5,2)) and you try to convert them to match a value with fewer (e.g., DECIMAL(3,1)), then you will have an overflow.

Consider the following SQL, which will throw an error:

DECLARE @Products TABLE (NAME NVARCHAR(30))
INSERT INTO @Products VALUES ('123.45')
INSERT INTO @Products VALUES ('12.0')

SELECT *
FROM @Products
WHERE NAME = 12.0

Now try this, which will work:

DECLARE @Products TABLE (NAME NVARCHAR(30))
INSERT INTO @Products VALUES ('123.45')
INSERT INTO @Products VALUES ('12.0')

SELECT *
FROM @Products
WHERE NAME = CAST(12.0 AS DECIMAL(5,2))

The difference between these is that SQL Server now accounts for cases where the table contains a number with a higher precision and/or scale than the one specified in the WHERE clause.

EDIT: further reading. Books Online states in the data type definition for DECIMAL and NUMERIC that:

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

Therefore, when you issue a query with the constant '12.0', it is being converted to the data type NUMERIC(3,1) and then trying to convert the NVARCHAR value to match.