Sql-server – “Conversion failed when converting the varchar value ‘NULL’ to data type int”

sql-server

When I insert records into a long table,I am getting the error
"Conversion failed when converting the varchar value 'NULL' to data type int"
How can I determine which column errors out?

The table has many fields and millions of records. Each iteration takes 10 minutes to bomb when I try to insert "NULL" string into integer column somewhere. I thought SQL server can tell me exact name of the column 🙁

Best Solution

If the value is truly NULL, there wouldn't be a conversion error. However, you have a string = "NULL" then you would get this error.

What you could do is...

NullIf(YourValueHere, 'NULL')

NullIf returns the value of the first parameter if it is not the same as the second parameter. If the parameters are the same, NullIf will return NULL.

Ex:

Select NullIf('Any Value', 'NULL')

Select NullIf('null','null')

The first will return 'Any Value' and the second will return NULL (not 'null')

Related Solutions

Sql – Conversion failed when converting the varchar value ‘1,’ to data type int

You need to split the 1,3,4 string returned by the subquery into separate int values. SQL Server does not have a built-in function to do it, but you can use this user-defined function.

Create the function dbo.Split in your database and then re-write your query as follows:

SELECT ID, Title
FROM c
WHERE ID IN
  (
    SELECT s
    FROM dbo.Split(',', '1,3,4')
  )

I replaced the subquery with example results 1,3,4 to shorten the query and make it easier to understand.

C# – Conversion failed when converting the varchar value ‘null’ to data type int

Make your data type Nullable<int> instead of int to allow the converted int nullable values, or on the sql select statement you can do that: ISNULL(yourcolumn, 0) to convert null to 0.

Best Solution

Related Solutions

Sql – Conversion failed when converting the varchar value ‘1,’ to data type int

C# – Conversion failed when converting the varchar value ‘null’ to data type int

Related Question