SQL: Conversion failed when converting the nvarchar value 'XXX' to data type int

Question

I am getting 'Conversion failed when converting the nvarchar value 'XXX' to data type int' error for no obvious reason and I don't know how to fix it. The view actually returns the correct data to stored procedure but it is giving me this error. I'm stuck. Anyone please give me some pointers?

SELECT          d.Code, a.Agency, a.Agency_job, a.OUCode, a.JobCode, d.OraUser, c.ProfileName
FROM            dbo.Agency AS a 
LEFT OUTER JOIN dbo.IdM AS d ON REPLACE(STR(a.OUCode, 3), ' ', '0') = d.OrganizationUnitCode AND a.JobCode = d.JobCode 
LEFT OUTER JOIN dbo.Profiles AS c ON c.JobCode = a.JobCode AND c.OUCode = a.OUCode
WHERE          (d.Code IS NOT NULL)

Column details -

 d.Code is nvarchar(32),
 d.OraUser is nvarchar(100),
 d.OrganizationUnitCode is nvarchar(16),
 a.Agency is int,
 a.Agency_job is int,
 a.OUCode is int,
 a.JobCode is int,
 c.ProfileName is varchar(100),
 c.OUCode is int,
 c.JobCode is int.

Does anyone maybe see a potential problem?

Answer 1

If you have OUCode > 999 in your tables, the STR(a.OUCode, 3) will return *** which when trying to convert to int would give you the error

Conversion failed when converting the varchar value '***' to data type int.

From books online : https://learn.microsoft.com/en-us/sql/t-sql/functions/str-transact-sql?view=sql-server-2017

When the expression exceeds the specified length, the string returns ** for the specified length.

The other issue I can see is : What is the datatype of JobCode in your IdM Table ?. Try this and see if it returns any results :

SELECT JobCode FROM dbo.IdM WHERE ISNUMERIC(JobCode) = 0

Answer 2

Below is the answer:

SELECT          d.Code, a.Agency, a.Agency_job, a.OUCode, a.JobCode, d.OraUser, c.ProfileName
FROM            dbo.Agency AS a 
LEFT OUTER JOIN dbo.IdM AS d ON CAST(REPLACE(STR(a.OUCode, 3), ' ', '0') AS NVARCHAR(16)) = d.OrganizationUnitCode AND a.JobCode = d.JobCode 
LEFT OUTER JOIN dbo.Profiles AS c ON c.JobCode = a.JobCode AND c.OUCode = a.OUCode
WHERE          (d.Code IS NOT NULL)

Also choose a correct value while using STR function.

Answer 3

It is only normal for the error to happen in your query!

REPLACE(STR(a.OUCode, 3), ' ', '0') is of string type but d.OrganizationUnitCode on the right side of join predicate is an int. So it cannot implicitly do the conversion here, either convert the int field to nvarchar or the other way around. you can try something like this:

cast(REPLACE(STR(a.OUCode, 3), ' ', '0') as int) = d.OrganizationUnitCode