Replace
where ReceiverId in(select * from dbo.Split('2,3'))
with
where ReceiverId in (2,3)
where cm.ChatId in (Select ChatId from tblChatDesc where ReceiverId = 2)
and cm.ChatId in (Select ChatId from tblChatDesc where ReceiverId = 3)
[Attempt 3!!]
I can't return temporary tables from a stored procedure in the version of SQL I'm currently using so you may need to tweak this code to make it work on your version and to simplify it
Basically, I'm using
group by
on both ChatId and ReceiverId to get a temporary table in the format
ChatId ReceiverId c
4 2 1
4 3 1
5 2 1
Then I'm using an outer select, also with a group by ... but this time I just want the ChatId and only if it appears in the temporary table exactly twice ...
select ChatId from
(
select cm.ChatId, ReceiverId, count(*) as c
from #ChatMaster cm
join #ChatDesc cd on cm.ChatId=cd.ChatId
join #MessageMaster mm on cd.ChatId=mm.ChatId
where MessengerId=1
and cm.ChatId in (Select ChatId from #ChatDesc where ReceiverId in (2,3))
group by cm.ChatId, ReceiverId
) tmp
group by ChatId
having count(*) = 2