ISNULL，合并函数

Question

如何将ISNULL与int值、varchar值一起使用

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

这个查询给出了一个错误

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

谢谢Sun

Answer 1

尝试将int列转换为varchar，

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

Answer 2

这取决于您想要返回的内容，如果您想要文本'Not Found‘，那么您可以使用CASE语句对您的SELECT执行以下操作：

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

由于MMD_ID是一个int，因此您还可以执行以下操作来返回int

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

这取决于你基于你的需求。

Answer 3

像这样使用它

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