MySQL Forums
Forum List  »  Stored Procedures

Procedure Question
Posted by: Matthew Fitzpatrick
Date: November 01, 2007 02:05PM

I have a "quick" question about a procedure I wrote shown below. The question is..... syntacticly/logically have I botched anything up here?
-------------------------------------------------------------

DELIMITER //

DROP PROCEDURE IF EXISTS `bosscms`.`myprocedure`//
CREATE PROCEDURE `bosscms`.`myprocedure` ()
BEGIN

DECLARE A,B,C,D,E,F,G,H,I,J,K,L,M,N VARCHAR(100);

DECLARE c_main CURSOR FOR
SELECT T1.column_11, T1.column_3, T1.column_32, T1.column_35, T1.column_40, T1.column_2, T1.column_5
FROM my_in_table as T1,
(SELECT column_11, column_3, MAX(column_32) as date_max
FROM my_in_table
GROUP BY column_11, column_3
) as V1
WHERE
T1.column_11 = V1.column_11
and T1.column_3 = V1.column_3
and T1.column_32 = date_max
and T1.column_32 != " "
and T1.column_3 != " ";

DECLARE c_sub CURSOR FOR
SELECT instance_id, field_id, value FROM cf_data;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET K = "1";

OPEN c_main;
OPEN c_sub;
FETCH c_main INTO A, B, C, D, E, F, G;
REPEAT
MyLoop: LOOP
FETCH c_sub INTO H, I, J;
IF I = "3"
THEN SET N = J;
END IF;
IF I = "4"
THEN UPDATE cf_data SET value = D
WHERE instance_id = H
AND N = B
AND field_id = "4";
END IF;
IF I = "5"
THEN UPDATE cf_data SET value = E
WHERE instance_id = H
AND N = B
AND field_id = "5";
END IF;
IF K = "1"
THEN
SET K = "0";
LEAVE MyLoop;
END IF;
END LOOP MyLoop;
FETCH c_main INTO A, B, C, D, E, F, G;
UNTIL K = "1"
END REPEAT;

CLOSE c_sub;
CLOSE c_main;

END//

DELIMITER ;

Thanks

-Matt



Edited 1 time(s). Last edit at 11/01/2007 02:53PM by Matthew Fitzpatrick.

Options: ReplyQuote


Subject
Views
Written By
Posted
Procedure Question
2024
November 01, 2007 02:05PM
918
November 01, 2007 09:31PM
992
November 02, 2007 04:10AM
973
November 07, 2007 12:09PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.