create procedure [dbo].[MssWebGetSmsUnreadMessageCount]
    @inSysUserId int
as
begin

    select UpdateCount = count(*)
        from SakariContactSysuserMap
        left outer join SakariIncomingMessage on SakariIncomingMessage.SakariContactFID =  SakariContactSysuserMap.SakariContactFID and
        SakariIncomingMessage.ImportedIntoMssAt > SakariContactSysuserMap.LastReadAt
        left outer join SakariOutgoingMessage on SakariOutgoingMessage.SakariContactFID = SakariContactSysuserMap.SakariContactFID and
        SakariOutgoingMessage.UpdatedInMssAt > SakariContactSysuserMap.LastReadAt and
            ( SakariOutgoingMessage.ErrorMessage is not null or SakariOutgoingMessage.SentBy = isnull( @inSysUserId, -1 ) )
        where SakariIncomingMessage.ImportedIntoMssAt > SakariContactSysuserMap.LastReadAt
        and SakariContactSysuserMap.SysuserFID = @inSysUserId
end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetSmsUnreadMessageCount] TO [MssExec]
GO