create procedure [dbo].[MssWebGetContactInfo]
    @inPhoneNumber varchar(16),
    @inSakariAccountId int
as
begin
    set nocount on
    select
        ContactId = SakariContact.SakariContactID,
        ConversationId = SakariConversation.SakariConversationID,
        ContactName = SakariContact.ContactName,
        OrderNo =
            coalesce(
                Orders.OrderNo,
                Orders.ArchivedOrderNo + ' [A]',
                dbo.FormatFirstNameLastName(Orders.LastName, Orders.FirstName ) )
    from SakariContact
    inner join SakariAccount on SakariContact.SakariAccountFID = SakariAccount.SakariAccountID
    left outer join SakariConversation on SakariContact.SakariContactID = SakariConversation.SakariContactFID
    left outer join SakariContactOrderMap on SakariContact.SakariContactID = SakariContactOrderMap.SakariContactFID
    left outer join Orders on SakariContactOrderMap.OrderFID = Orders.PriKey
    where
        SakariContact.PhoneNumber = @inPhoneNumber and
        SakariAccount.SakariAccountID = @inSakariAccountId
end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetContactInfo] TO [MssExec]
GO