Cusors in Stored Procedures + Where Clause

[Chris Lynch]

Hi,

I have a Stored Procedure called sp_import_tax(). It received two input variables from a PHP file.

The Stored Procedure is used to update tables based on a Staging table that data is uploaded too.

Multiple Users can upload data to the table called etl_taxpro - the final column in each row contains an area code. When the user executes the Stored Procedure the area code is passed to it and I want to then use the passed Area Code when I am declaring my cusor so only the specific users rows are used.

See Store Procedure code below



CREATE DEFINER=`root`@`%` PROCEDURE `sp_import_taxpro`(current_user_id VARCHAR(36), areacode VARCHAR(36))
BEGIN

DECLARE tax_number VARCHAR(255);
DECLARE year_end VARCHAR(255);
DECLARE filing_date_f11 DATE DEFAULT '1970-01-01';
DECLARE completion_date_f11 DATE DEFAULT '1970-01-01';
DECLARE submission_date_f11 DATE DEFAULT '1970-01-01';
DECLARE f11_ack VARCHAR(255);
DECLARE submission_date_46g DATE DEFAULT '1970-01-01';
DECLARE 46g_ack VARCHAR(255);
DECLARE prelim_date_paid DATE DEFAULT '1970-01-01';
DECLARE prelim_date DATE DEFAULT '1970-01-01';
DECLARE user_area_code VARCHAR(255);
DECLARE done INT DEFAULT 0;
DECLARE cursor1 CURSOR FOR
SELECT
TaxNumber,
YearEnd,
DATE_FORMAT(STR_TO_DATE(FilingDateF11, '%d/%m/%Y %H:%i:%S'), '%Y-%m-%d') AS filing_date_f11,
DATE_FORMAT(STR_TO_DATE(CompletionDateF11, '%d/%m/%Y %H:%i:%S'), '%Y-%m-%d') AS completion_date_f11,
DATE_FORMAT(STR_TO_DATE(SubmissionDateF11, '%d/%m/%Y %H:%i:%S'), '%Y-%m-%d') AS submission_date_f11,
F11Ack,
DATE_FORMAT(STR_TO_DATE(SubmissionDate46G, '%d/%m/%Y %H:%i:%S'), '%Y-%m-%d') AS submission_date_46g,
46GAck,
DATE_FORMAT(STR_TO_DATE(PrelimTaxDatePaid, '%d/%m/%Y %H:%i:%S'), '%Y-%m-%d') AS prelim_date_paid,
DATE_FORMAT(STR_TO_DATE(PrelimTax, '%d/%m/%Y %H:%i:%S'), '%Y-%m-%d') AS prelim_date,
AreaCode
FROM etl_taxpro
WHERE AreaCode=area_code;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cursor1;
REPEAT FETCH cursor1 INTO tax_number,year_end,filing_date_f11,completion_date_f11,submission_date_f11,f11_ack,submission_date_46g,46g_ack,prelim_date_paid,prelim_date,user_are_code;
IF NOT done THEN

/** The code I execute here will contain all data from the etl_taxpro and not just the date matching area_code
**/


END IF;
UNTIL done END REPEAT;
CLOSE cursor1;

END$$

DELIMITER ;


Is what I want to achieve possible.