Champ Champ - 1 month ago 7
SQL Question

Parse Comma(,) String in MySql stored procedure

Stored procedure:

CREATE PROCEDURE lead_to_loan(xReffID_list text)


I want to use this
xReffID_list
variable in a select statement as

SELECT * FROM XXXX where xreffID IN (xReffID_list);


but the xreffID is a
int
Variable

How Can I use
xReffID_list text
which is a string of comma separated numbers in the
IN
condition for
int
variables ?

Stored procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS lead_to_loan$$
CREATE PROCEDURE lead_to_loan(XRefID_list text)
BEGIN

DECLARE loanCount int(11) default 0;
DECLARE matchCount int(11) default 0;
DECLARE loan_XRefID int(11);
DECLARE loan_LEADS360ID int(11);
DECLARE loan_email varchar(100);
DECLARE loan_phone varchar(30);
DECLARE loan_cellphone varchar(20);
DECLARE loan_workphone varchar(20);
DECLARE loan_closeDate datetime;
DECLARE loan_FundedDate datetime;

DECLARE lead_id int(11);
DECLARE lead_RefId varchar(100);
DECLARE lead_Email varchar(100);
DECLARE lead_DayPhone varchar(50);
DECLARE lead_EveningPhone varchar(20);
DECLARE lead_Cellphone varchar(20);
DECLARE lead_DateAdded varchar(30);

DECLARE done boolean default false;
DECLARE startTime datetime;

DECLARE cursor_loanDetail CURSOR FOR
SELECT XRefID,LEADS360ID,email,phone,cellphone,workphone,closeDate,FundedDate
FROM fsbcorponline.view_loandetail where find_in_set(XRefID, XRefID_list) > 0;

DECLARE cursor_loanMatchLeads CURSOR FOR
SELECT id,RefId,Email,DayPhone,EveningPhone,Cellphone,DateAdded
FROM fsbcorponline.leads360leads
WHERE RefId !="" AND RefId IS NOT NULL AND RefId =loan_LEADS360ID AND loan_LEADS360ID>0 OR
Email !="" AND Email IS NOT NULL AND Email =loan_email OR
DayPhone !="" AND DayPhone IS NOT NULL AND DayPhone = loan_workphone OR
EveningPhone !="" AND EveningPhone IS NOT NULL AND EveningPhone= loan_phone OR
Cellphone !="" AND Cellphone IS NOT NULL AND Cellphone =loan_cellphone;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

set startTime = now();

OPEN cursor_loanDetail;
cursor_loanDetail_loop: loop
fetch cursor_loanDetail into loan_XRefID,loan_LEADS360ID,loan_email,loan_phone,loan_cellphone,loan_workphone,loan_closeDate,loan_FundedDate;
if done then
set done = false;
leave cursor_loanDetail_loop;
END if;
SET loanCount = loanCount+1;

OPEN cursor_loanMatchLeads;
cursor_loanMatchLeads_loop: loop
fetch cursor_loanMatchLeads into lead_id,lead_RefId,lead_Email,lead_DayPhone,lead_EveningPhone,lead_Cellphone,lead_DateAdded;
if done then
set done = false;
leave cursor_loanMatchLeads_loop;
END if;

SET matchCount = matchCount+1;
INSERT INTO `fsbcorponline`.`leads_to_loan`(`lead_id`,`lead_RefId`,`lead_Email`,`lead_DayPhone`,`lead_EveningPhone`,`lead_Cellphone`,`lead_DateAdded`,`loan_XRefID`,`loan_LEADS360ID`,`loan_email`,`loan_phone`,`loan_cellphone`,`loan_workphone`,`loan_closeDate`,`loan_FundedDate`)
VALUES(lead_id,lead_RefId,lead_Email,lead_DayPhone,lead_EveningPhone,lead_Cellphone,lead_DateAdded,loan_XRefID,loan_LEADS360ID,loan_email,loan_phone,loan_cellphone,loan_workphone,loan_closeDate,loan_FundedDate)
ON duplicate key update loan_updateCount = loan_updateCount +1 ;
leave cursor_loanMatchLeads_loop;
END loop cursor_loanMatchLeads_loop;
CLOSE cursor_loanMatchLeads;



END loop cursor_loanDetail_loop;
close cursor_loanDetail;
INSERT INTO `fsbcorponline`.`log`(`processName`,`pageName`,`path`,`status`,`note`,`processStartTime`,`processEndTime`)
VALUES('Store Procedure','Lead_to_Loan','Database','1',CONCAT('Loan Matches ',matchCount,' of total ',loanCount),startTime,now());
END$$
DELIMITER ;

Answer

You can use find_in_set to do this:

SELECT * FROM XXXX WHERE find_in_set(xreffID, xreffID_list) > 0
Comments