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

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:

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:

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:

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:

-- 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:

-- 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:

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:

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.