CREATE PROCEDURE [dbo].[MssWebQuickFindCustomer]
    @inSearchString varchar(128),
    @inSysUserId int
AS
BEGIN
SET NOCOUNT ON

    DECLARE @availableBranches TABLE
    (
        BranchPriKey int UNIQUE not null
    )

    ;with OrderInfoSecurityModule as
    (
        SELECT SecModules.ModulePriKey
        FROM SecModules where SecModules.Description = 'Order Information' and
        SecModules.Active = 1
    )
    INSERT INTO @availableBranches (BranchPriKey )
    SELECT SecProfileDetail.BranchPriKey
    FROM UserAccess
    cross join OrderInfoSecurityModule
    inner join SecProfileDetail on
        UserAccess.SecProfilePriKey = SecProfileDetail.SecProfilePriKey and
        OrderInfoSecurityModule.ModulePriKey = SecProfileDetail.ModulePriKey
    WHERE UserAccess.SysUserID = @inSysUserID;


    DECLARE @theWildCardSearchString varchar(32) = @inSearchString + '%';
    -- Added UNION ALL because of using seperate indexing for Last Name and First name
    WITH OrdersData AS (
  SELECT top 8
        dbo.FormatFirstNameLastName (o.FirstName,o.LastName) as CustomerName,
        o.PriKey,
        o.OrderNo,
        o.CreatedOn
    FROM Orders o
    inner join @availableBranches a  on o.BranchPriKey = a.BranchPriKey
    where o.LastName like @theWildCardSearchString

    UNION ALL
    
    SELECT top 8
        dbo.FormatFirstNameLastName (o.FirstName,o.LastName) as CustomerName,
        o.PriKey,
        o.OrderNo,
        o.CreatedOn
    FROM Orders o
    inner join @availableBranches a  on o.BranchPriKey = a.BranchPriKey
    where o.FirstName like @theWildCardSearchString
)
SELECT CustomerName,
        PriKey,
        OrderNo,
        CreatedOn FROM OrdersData
order by CreatedOn desc    
END
GO
GRANT EXECUTE ON  [dbo].[MssWebQuickFindCustomer] TO [MssExec]
GO