Passing value beteen declared variables within cursor loop

[Michael Ayres]

I am having trouble with manipulating declared local variables within a stored procedure's cursor fetch loop.

--- pseudo code ----

I have Declare v_subcat_id INT (11)
Declare var2 as ...

Declare mycursor cursor as
select ,,, subcat_id from table1
While bla bla
FETCH mycursor INTO .., v_subcat_id
if v_subcat_id IS NULL THEN
var2 = v_subcat_id

....

UPDATE TABLE1 SET somefield = var2, otherfield=v_subcat_id;

--- error does not seem to pass local cursor var values to table update

end while


//////////////////

actual code

CREATE PROCEDURE updatecore1()
BEGIN
DECLARE v_nist_id INT(11);
DECLARE v_refdesc_fk INT(11);
DECLARE v_subcat_id INT(11);
DECLARE v_subcatdesc VARCHAR (101);
DECLARE v_prev_id INT (11);
DECLARE v_comments VARCHAR(101);
DECLARE update_count INT DEFAULT 0;
DECLARE row_not_found TINYINT DEFAULT FALSE;

DECLARE nist_core CURSOR FOR
SELECT ID, subcategory, subcat_id
FROM csf_core;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET row_not_found = TRUE;

OPEN nist_core;
WHILE row_not_found = FALSE DO
FETCH nist_core INTO v_nist_id, v_subcatdesc, v_subcat_id;
IF v_subcat_id IS NOT NULL THEN
v_prev_id = v_subcat_id;
ELSEIF v_subcat_id IS NULL THEN
v_subcat_id = v_prev_id;
UPDATE csf_core
SET subcat_id = v_subcat_id;
END IF;
SET update_count = update_count + 1;
END WHILE;
CLOSE nist_core;
SELECT CONCAT(update_count, ' Rows updated');

END//

DELIMITER ;

Michael Ayres
Senior Technical Project Manager
Cyber Security Coordination Center

Michael Ayres, MS, PMP, ACP, CISSP
University of California Office of the President (contractor)
1111 Franklin Street #7109 Oakland, Ca.94607
[e-mail] | [phone] | [phone] Mobile