-
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
