ISNULL, COALESCE functions

How do I use ISNULL with int values, varchar values

 select Complaint.complaintProfileId,ISNULL(T2.MMB_id,'Notfound') as MMBId
from complaints c join T2..
on T2.sno = c.sn开发者_开发技巧o

This query gives me an error

Conversion failed when converting the varchar value 'Notfound' to data type int.
MMB_id is a int

Thanks Sun

Try converting your int column to a varchar,

SELECT Complaint.ComplaintProfileId, ISNULL(CONVERT(varchar(11), T2.MMB_id), 'Notfound') AS MMBId
FROM complaints c join T2..
ON T2.sno = c.no

Use it like

select Complaint.complaintProfileId,ISNULL(T2.MMB_id,0) as MMBId from complaints c join T2 on T2.sno = c.sno 

It depends on what you want to return, if you want the text 'Not Found' then you can do the following to your SELECT with a CASE statement:

select Complaint.complaintProfileId,
    CASE
        WHEN T2.MMB_id is not null Then T2.MMB_id
        ELSE 'Not Found'
    END  as MMBId
from complaints c join T2..
on T2.sno = c.sno

Since the MMD_ID is an int you can also do the following to return an int:

select Complaint.complaintProfileId, IsNull(T2.MMB_id, 0) as MMBId
    from complaints c join T2..
    on T2.sno = c.sno

It's up to your based on your needs.

For ISNULL, the first value determines the data type of the expression and the 2nd value is converted to the type of the first expression. So it is trying to convert your varchar to an int. I think COALESCE takes the data type with the highest precedence of all the expressions in the COALESCE, which I think in this case would still be the int. So either way I think you need to cast the int to varchar.