SQL Server Error Messages - Msg 245

Error Message

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value to a column of data type int.

Causes:

There are many ways this error may be encountered but the common reason is that you are trying to convert, either implicitly or explicitly, a varchar value to an int data type and the varchar value cannot be converted to an int data type. You may have a varchar column that may have integer values on most records but where some have non-integer values.

One other common reason why this is encountered is when creating a dynamic SQL statement and the query involves combining a varchar variable with an integer variable.

DECLARE @SQL VARCHAR(2000)
DECLARE @ID INT

SET @ID = 124
SET @SQL = 'SELECT * FROM [dbo].[Customers] WHERE [ID] = ' + @ID

The reason why the error is encountered in this scenario is because an integer data type has a higher precedence over a varchar data type. Since the integer data type has a higher precedence, the varchar data type is implicitly converted by SQL Server to an integer data type, and not the other way around as you would have assumed.

Solution / Work Around:

For the case of a varchar column that contains integer values but with a few non-integer values, you can use the ISNUMERIC function to determine if the column can be converted to an integer value or not. To determine the rows where the column cannot be converted to an integer, you can do the following query:

SELECT * FROM [dbo].[Table1] WHERE ISNUMERIC([VarcharIntColumn]) = 0

For the case of the dynamic SQL wherein a varchar variable is concatenated with an integer variable, you have to explicitly convert the integer variable to a varchar data type using either the CAST or CONVERT function.

DECLARE @SQL VARCHAR(2000)
DECLARE @ID INT

SET @ID = 124
SET @SQL = 'SELECT * FROM [dbo].[Customers] WHERE [ID] = ' + CAST(@ID AS VARCHAR(10))