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!

If `NAME` is a `varchar()` then compare it to a `varchar()`, not a number: `NAME = '12.0'`. — Gordon Linoff, Dec 02 '14 at 12:06
@GordonLinoff, yes, that will work, but why 112.0 works fine but 12.0 not? — Harry, Dec 02 '14 at 12:08
No settings and all. In your dynamic query handle with CAST or CONVERT function. select * FROM #temp WHERE col1=cast (12.0 as nvarchar(30)).....As Gordon Said EQUAL to operator excepts datatype should be same on either side of the sign. — knkarthick24, Dec 02 '14 at 12:23

score 3 · Accepted Answer · edited May 23 '17 at 12:16

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.

Thanks Ahiggins, that explains a lot, the greatest number in the table was 500 so 112.0 works, after I put 1000 in the table, 112.0 failed. — Harry, Dec 02 '14 at 13:15

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

1 Answers1