Conversion failed when converting the varchar value [value] to data type int.

We had a failure today and it brought up a couple of interesting points. I'll put this into my beginner category, but you might take something away from this post regardless. Run the following script:

USE tempdb;
GO
DROP TABLE tbl_Test
GO
CREATE TABLE tbl_Test
       (ID int IDENTITY(1,1) NOT NULL PRIMARY KEY
       ,charCol char(2)
       )
GO

INSERT INTO tbl_Test (charCol) VALUES (1)
GO
INSERT INTO tbl_Test (charCol) VALUES  (2)
GO
INSERT INTO tbl_Test (charCol) VALUES ('3a')
GO

This is all fine - love row constructors - but go ahead and try this one:

INSERT INTO tbl_Test (charCol) VALUES (1), (2), ('3a')
GO

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '3a' to data type int.

How about this one?

INSERT INTO tbl_Test (charCol) VALUES ('a')
GO
INSERT INTO tbl_Test (charCol) VALUES  ('b')
GO
INSERT INTO tbl_Test (charCol) VALUES (3)
GO
INSERT INTO tbl_Test (charCol) VALUES ('a'), ('b'), (3)
GO

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'a' to data type int.

I wouldn't swear to it, but I'm guess that what is happening here is that data type precedence is causing this failure. Despite the order of INSERT and types, the higher precedence integer causes this to fail when inserting via a row constructor.

Which brings up another point - check this one out:

USE tempdb;
GO
DROP TABLE tbl_Test
GO
CREATE TABLE tbl_Test
       (ID int IDENTITY(1,1) NOT NULL PRIMARY KEY
       ,charCol char(2)
       )
GO
INSERT INTO tbl_Test (charCol) VALUES (11)
GO
INSERT INTO tbl_Test (charCol) VALUES  (22)
GO
INSERT INTO tbl_Test (charCol) VALUES ('AA')
GO

SELECT MAX(ID) as charCol
FROM tbl_Test
WHERE charCol=11
GROUP BY ID

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'AA' to data type int.

In this case, we needed to use test the data type in the WHERE clause beforehand, or checking the type before inserting into the table. Also, it's good practice to not let the implict conversion from int to char kick in; if you are inserting an integer into a char field, consider putting the tick marks '' around the integer, depening on the action that you want the process to take downstream.

Lee

----------------------

http://msdn.microsoft.com/en-us/library/ms174335.aspx