CREATE PROCEDURE [usp_TE_getBOAcctLookUp]
(
@AccountNum    VARCHAR(50)
)
AS
DECLARE    @vTypeList    VARCHAR(500),
        @vMatchResult    VARCHAR(10),
        @vMatchList    VARCHAR(500),
        @vPos        INT,
        @vEntry_ID    INT,
        @vRegExMatch    VARCHAR(30),
        @vSQLQuery    NVARCHAR(4000)
-- get a possbile list of account types to match
SELECT     @vTypeList = COALESCE(@vTypeList + ',', '') + CAST(Entry_ID AS VARCHAR)
FROM         TEBORAcctRegEx
WHERE    RegExMatch IS NOT NULL
SELECT    @vMatchList = ''
SELECT    @vTypeList = @vTypeList + ','
--loop through request list
WHILE CHARINDEX(',', @vTypeList) > 0
    BEGIN
        --get next comma position for of the list
        SELECT     @vPOS = CHARINDEX(',', @vTypeList)
    
        --get values to be used in queries below
        SELECT     @vEntry_ID = LEFT(@vTypeList,@vPOS - 1)
        
        SELECT    @vRegExMatch = RegExMatch
        FROM         TEBORAcctRegEx
        WHERE    Entry_ID = @vEntry_ID
        EXEC     master.dbo.xp_regex_match @AccountNum,@vRegExMatch,@vMatchResult OUTPUT
            
        -- if it matches then add entry id to list of matched values
        IF @vMatchResult = 'Matched'
            BEGIN
                SELECT @vMatchList = @vMatchList + CAST(@vEntry_ID AS VARCHAR) + ','
            END
        SELECT @vTypeList = STUFF(@vTypeList,1,@vPOS,'')
    END
-- remove the last comma
SELECT @vMatchList = LEFT(@vMatchList,LEN(@vMatchList)-1)
-- create dynamic query to query db for list of possible matches    
SELECT    @vSQLQuery =    '    
                SELECT     Company,
                        Biller_ID,
                        BillerName,
                        AccountExample,
                        AccountExplanation,
                        ContactEMail,
                        ContactPhone,
                        ContactFax,
                        ContactInfo
                FROM        TEBORAcctRegEx
                WHERE    Entry_ID IN ('+ @vMatchList+')
                ORDER BY    BillerName
                '
-- execute dynamic query
EXEC sp_executesql @vSQLQuery
GO