Print

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:
copyraw
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
  1.  Table: DMExtractEmployeeTable 
  2.  ------------------------------------------- 
  3.  ID     Name         EmpType       CodeType 
  4.  1      Joe          Manager       0011 
  5.  2      John         Tape-Monkey   8475 
  6.  3      Fred         Director      7749 
  7.  4      Bill         Manager       0011 
  8.  5      Another      Tape-Monkey   8475 
  9.  6      Joe          Tape-Monkey   8475 
  10.   
  11.   
  12.  Table: DMExtractReferenceTable 
  13.  ------------------------------ 
  14.  ID     MapIn        MapOut 
  15.  1      0011         71 
  16.  2      8475         84 
  17.  3      7749         63 
  18.  4      B110         92 
  19.   
  20.  -- 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:
copyraw
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
  1.  SELECT 
  2.          r1.MapIn 
  3.          ( 
  4.                  SELECT 
  5.                          (COUNT(e2.EmpType)-1) AS MyCount 
  6.                  FROM 
  7.                          DMExtractEmployeeTable e2 
  8.                  LEFT JOIN 
  9.                          DMExtractReferenceTable r2 
  10.                          ON e2.CodeType=r2.MapIn            --  THIS IS THE LINE CAUSING THE PROBLEM!!! 
  11.                  WHERE 
  12.                          e2.ID=e1.ID 
  13.                  GROUP BY 
  14.                          e2.ID,r2.MapIn 
  15.          ) AS MatchingRecordsCount 
  16.  FROM 
  17.          DMExtractEmployeeTable1 e1 
  18.  LEFT JOIN 
  19.          DMExtractReferenceTable r1 
  20.          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:
copyraw
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
  1.  SELECT 
  2.          r1.MapIn 
  3.          ( 
  4.                  SELECT 
  5.                          (COUNT(e2.EmpType)-1) AS MyCount 
  6.                  FROM 
  7.                          DMExtractEmployeeTable e2 
  8.                  LEFT JOIN 
  9.                          DMExtractReferenceTable r2 
  10.                          ON e2.CodeType LIKE r2.MapIn               --  FIXED!!! 
  11.                  WHERE 
  12.                          e2.ID=e1.ID 
  13.                  GROUP BY 
  14.                          e2.ID,r2.MapIn 
  15.          ) AS MatchingRecordsCount 
  16.  FROM 
  17.          DMExtractEmployeeTable1 e1 
  18.  LEFT JOIN 
  19.          DMExtractReferenceTable r1 
  20.          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:
copyraw
-- 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..."
  1.  -- Initial Query 
  2.  SELECT 
  3.      CASE 
  4.          WHEN emp.ContributionValue=1    THEN 5010 
  5.          WHEN emp.ContributionValue=2    THEN 5011 
  6.          WHEN emp.ContributionValue=3    THEN 5012 
  7.          ELSE 9999 
  8.      END 
  9.  FROM [dbo].[DMExtractEmployeeTable] emp 
  10.  INNER JOIN [dbo].[DMExtractReferenceTable] ref 
  11.      ON RIGHT(emp.[PayCode],3) = ref.RefMappedValue 
  12.      AND ref.[RefSheet] = 'myPayrollCategory' 
  13.      AND ref.[RefSystem] = 'myPayrollSystem' 
  14.   
  15.  -- Yields "Conversion failed..." 

Working version:
copyraw
-- 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'
  1.  -- Corrected query (output values enclosed in apostrophes) 
  2.  SELECT 
  3.      CASE 
  4.          WHEN emp.ContributionValue=1    THEN '5010' 
  5.          WHEN emp.ContributionValue=2    THEN '5011' 
  6.          WHEN emp.ContributionValue=3    THEN '5012' 
  7.          ELSE '9999' 
  8.      END 
  9.  FROM [dbo].[DMExtractEmployeeTable] emp 
  10.  INNER JOIN [dbo].[DMExtractReferenceTable] ref 
  11.      ON RIGHT(emp.[PayCode],3) = ref.RefMappedValue 
  12.      AND ref.[RefSheet] = 'myPayrollCategory' 
  13.      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:
copyraw
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
  1.  LTRIM(RTRIM(rm.Name + ', ' + rm.[Zone] + ', ' + rm.[Capacity] + ', ' + rm.[Type] + ', ' + rm.[DeptId])) AS RoomDetails 
  2.   
  3.  // 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:
copyraw
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.
  1.  LTRIM(RTRIM(rm.Name + ', ' + rm.[Zone] + ', ' + CONVERT(VARCHAR(4), rm.[Capacity]) + ', ' + rm.[Type] + ', ' + rm.[DeptId])) AS RoomDetails 
  2.   
  3.  // success! added CONVERT(VARCHAR(4) to the room capacity which was of datatype int. 


Category: Transact-SQL :: Article: 508