T-SQL Conversion failed when converting the varchar to data type int

: Joel Lipman; Transact-SQL; 14 May 2013; Hits: 32306

What?
A very quick note in case I forget this one. If you are trying to join two tables and receiving the error "Conversion failed when converting the varchar value 'B110' to data type int" then read on.

How?
So where does the 'B110' string come from, well from one of our tables which looks similar to the following:

copy raw

Table: DMExtractEmployeeTable
-------------------------------------------
ID     Name         EmpType       CodeType
1      Joe          Manager       0011
2      John         Tape-Monkey   8475
3      Fred         Director      7749
4      Bill         Manager       0011
5      Another      Tape-Monkey   8475
6      Joe          Tape-Monkey   8475


Table: DMExtractReferenceTable
------------------------------
ID     MapIn        MapOut
1      0011         71
2      8475         84
3      7749         63
4      B110         92

-- so we have 1 director, 2 managers, and 3 tape-monkeys

Table: DMExtractEmployeeTable
-------------------------------------------
ID Name EmpType CodeType
1 Joe Manager 0011
2 John Tape-Monkey 8475
3 Fred Director 7749
4 Bill Manager 0011
5 Another Tape-Monkey 8475
6 Joe Tape-Monkey 8475
Table: DMExtractReferenceTable
------------------------------
ID MapIn MapOut
1 0011 71
2 8475 84
3 7749 63
4 B110 92
-- so we have 1 director, 2 managers, and 3 tape-monkeys

Now suppose we had a convoluted query which needs to join the two tables so that we get the "MapOut" value:

copy raw

SELECT
        r1.MapIn
        (
                SELECT
                        (COUNT(e2.EmpType)-1) AS MyCount
                FROM
                        DMExtractEmployeeTable e2
                LEFT JOIN
                        DMExtractReferenceTable r2
                        ON e2.CodeType=r2.MapIn            --  THIS IS THE LINE CAUSING THE PROBLEM!!!
                WHERE
                        e2.ID=e1.ID
                GROUP BY
                        e2.ID,r2.MapIn
        ) AS MatchingRecordsCount
FROM
        DMExtractEmployeeTable1 e1
LEFT JOIN
        DMExtractReferenceTable r1
        ON e1.CodeType=r1.MapIn

SELECT
r1.MapIn
(
SELECT
(COUNT(e2.EmpType)-1) AS MyCount
FROM
DMExtractEmployeeTable e2
LEFT JOIN
DMExtractReferenceTable r2
ON e2.CodeType=r2.MapIn -- THIS IS THE LINE CAUSING THE PROBLEM!!!
WHERE
e2.ID=e1.ID
GROUP BY
e2.ID,r2.MapIn
) AS MatchingRecordsCount
FROM
DMExtractEmployeeTable1 e1
LEFT JOIN
DMExtractReferenceTable r1
ON e1.CodeType=r1.MapIn

So why do I get the error: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'B110' to data type int. I'm not using any of the non-numeric values to map to... Well the system thinks I might so it fails. I realised then the equals sign ("=") could be changed to a "LIKE" which will mean both values are expected to be a string.

So with this in mind:

copy raw

SELECT
        r1.MapIn
        (
                SELECT
                        (COUNT(e2.EmpType)-1) AS MyCount
                FROM
                        DMExtractEmployeeTable e2
                LEFT JOIN
                        DMExtractReferenceTable r2
                        ON e2.CodeType LIKE r2.MapIn               --  FIXED!!!
                WHERE
                        e2.ID=e1.ID
                GROUP BY
                        e2.ID,r2.MapIn
        ) AS MatchingRecordsCount
FROM
        DMExtractEmployeeTable1 e1
LEFT JOIN
        DMExtractReferenceTable r1
        ON e1.CodeType=r1.MapIn

SELECT
r1.MapIn
(
SELECT
(COUNT(e2.EmpType)-1) AS MyCount
FROM
DMExtractEmployeeTable e2
LEFT JOIN
DMExtractReferenceTable r2
ON e2.CodeType LIKE r2.MapIn -- FIXED!!!
WHERE
e2.ID=e1.ID
GROUP BY
e2.ID,r2.MapIn
) AS MatchingRecordsCount
FROM
DMExtractEmployeeTable1 e1
LEFT JOIN
DMExtractReferenceTable r1
ON e1.CodeType=r1.MapIn

So simply change the "=" to a "LIKE".

Scenario #2
So when the above has been addressed or we've tried casting as varchars all over the place, check the value that gets outputted is also a literal string. So here's some ok code but it will fail if my reference data has a non-numeric value in it:

copy raw

-- Initial Query
SELECT
	CASE
		WHEN emp.ContributionValue=1	THEN 5010
		WHEN emp.ContributionValue=2	THEN 5011
		WHEN emp.ContributionValue=3	THEN 5012
		ELSE 9999
	END
FROM [dbo].[DMExtractEmployeeTable] emp
INNER JOIN [dbo].[DMExtractReferenceTable] ref
	ON RIGHT(emp.[PayCode],3) = ref.RefMappedValue
	AND ref.[RefSheet] = 'myPayrollCategory'
	AND ref.[RefSystem] = 'myPayrollSystem'

-- Yields "Conversion failed..."

-- Initial Query
SELECT
CASE
WHEN emp.ContributionValue=1 THEN 5010
WHEN emp.ContributionValue=2 THEN 5011
WHEN emp.ContributionValue=3 THEN 5012
ELSE 9999
END
FROM [dbo].[DMExtractEmployeeTable] emp
INNER JOIN [dbo].[DMExtractReferenceTable] ref
ON RIGHT(emp.[PayCode],3) = ref.RefMappedValue
AND ref.[RefSheet] = 'myPayrollCategory'
AND ref.[RefSystem] = 'myPayrollSystem'
-- Yields "Conversion failed..."

Working version:

copy raw

-- Corrected query (output values enclosed in apostrophes)
SELECT
	CASE
		WHEN emp.ContributionValue=1	THEN '5010'
		WHEN emp.ContributionValue=2	THEN '5011'
		WHEN emp.ContributionValue=3	THEN '5012'
		ELSE '9999'
	END
FROM [dbo].[DMExtractEmployeeTable] emp
INNER JOIN [dbo].[DMExtractReferenceTable] ref
	ON RIGHT(emp.[PayCode],3) = ref.RefMappedValue
	AND ref.[RefSheet] = 'myPayrollCategory'
	AND ref.[RefSystem] = 'myPayrollSystem'

-- Corrected query (output values enclosed in apostrophes)
SELECT
CASE
WHEN emp.ContributionValue=1 THEN '5010'
WHEN emp.ContributionValue=2 THEN '5011'
WHEN emp.ContributionValue=3 THEN '5012'
ELSE '9999'
END
FROM [dbo].[DMExtractEmployeeTable] emp
INNER JOIN [dbo].[DMExtractReferenceTable] ref
ON RIGHT(emp.[PayCode],3) = ref.RefMappedValue
AND ref.[RefSheet] = 'myPayrollCategory'
AND ref.[RefSystem] = 'myPayrollSystem'

Yes well obvious to some but then you google the above and you get half a million results. That's a lot of reading so here's the simple fix for my issue:

Returning an error:

copy raw

LTRIM(RTRIM(rm.Name + ', ' + rm.[Zone] + ', ' + rm.[Capacity] + ', ' + rm.[Type] + ', ' + rm.[DeptId])) AS RoomDetails

// yields Conversion failed when converting the varchar value ', ' to data type int

LTRIM(RTRIM(rm.Name + ', ' + rm.[Zone] + ', ' + rm.[Capacity] + ', ' + rm.[Type] + ', ' + rm.[DeptId])) AS RoomDetails
// yields Conversion failed when converting the varchar value ', ' to data type int

My issue was silly T-SQL concatenation which requires plus signs and thinks it's super clever when it tries to add a number and a string together; then remembers it's a Microsoft by-product and realises it can't.

Solved by checking the datatypes for each field in the concatenation string:

copy raw

LTRIM(RTRIM(rm.Name + ', ' + rm.[Zone] + ', ' + CONVERT(VARCHAR(4), rm.[Capacity]) + ', ' + rm.[Type] + ', ' + rm.[DeptId])) AS RoomDetails

// success! added CONVERT(VARCHAR(4) to the room capacity which was of datatype int.

LTRIM(RTRIM(rm.Name + ', ' + rm.[Zone] + ', ' + CONVERT(VARCHAR(4), rm.[Capacity]) + ', ' + rm.[Type] + ', ' + rm.[DeptId])) AS RoomDetails
// success! added CONVERT(VARCHAR(4) to the room capacity which was of datatype int.