Concatenating int and nvarchar column in TSQL

Question

I'm using MSSQL EXPRESS 2008 and I'm trying to concatenate the record's ID column with it's description column using the following query

SELECT CAST(GotoviProizvodi.ID as nvarchar(4)) + ' - ' + CAST(GotoviProizvodi.Opis as nvarchar(max)) AS Opis 
FROM GotoviProizvodi,Recept 
WHERE Recept.ID=@ID 
  AND Recept.Proizvod=GotoviProizvodi.Opis

GotoviProizvodi.ID is defined in the schema as int   -- the ID column
GotoviProizvodi.Opis is defined as nvarchar(200)   -- the description column

But, when I try to execute the query it yields this:

Msg 245, Level 16, State 1, Procedure GetProizvodByReceptId, Line 2 Conversion failed when converting the nvarchar value 'Test' to data type int.

Why is it trying to convert it into int, when I'm explicitly telling it to convert it into nvarchar? Any workarounds?

Solution 1

are you sure the error is related to the casts and not to the where conditions? check that the column types are matching

Solution 2

i think this'll do :

SELECT (CONVERT(NVARCHAR, GotoviProizvodi.ID) + ' - ' + GotoviProizvodi.Opis) AS Opis 
FROM GotoviProizvodi,Recept 
WHERE Recept.ID=@ID 
  AND Recept.Proizvod=GotoviProizvodi.Opis

Comments

[Taryn]

what is the datatype of Recept.Proizvod on the join?

[Kiro Coneski]

You're right. It should have been Recept.Proizvod=GotoviProizvodi.ID.