|
|
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/Workaround:
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))
|