Stored Procedure
"Melissa Dougherty" <[email protected]>
| Newsgroups | gmane.comp.db.mysql.windows |
|---|---|
| Message-ID | <005801c690b0$1d13d280$6400a8c0@denterprise1> |
I'm still new to the MySQL SPs.... Does anyone see a problem with this code?
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`test_AuditRun_SP`$$
CREATE PROCEDURE `test`.`test_AuditRun_SP`(OUT OblDocNo varchar(50), OUT OBLCNT INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE Obldoctype varchar(10);
DECLARE Obldocno varchar(50);
DECLARE Oblrefdocno varchar(50);
DECLARE Obldollamt varchar(50);
DECLARE Recdoctype varchar(10);
DECLARE Recdocno varchar(50);
DECLARE Recrefdocno varchar(50);
DECLARE Recdollamt varchar(50);
DECLARE curObl1 CURSOR FOR SELECT doc_type,doc_no,ref_doc_no,concat('-',gv_dollar_amt) FROM test.ar_no_ins_outs_temp WHERE doc_type in ('PV','GV');
DECLARE curRec2 CURSOR FOR SELECT doc_type,doc_no,ref_doc_no,gv_dollar_amt FROM test.ar_no_ins_outs_temp WHERE doc_type in ('RG','RC','RT');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN curObl1;
OPEN curRec2;
REPEAT
FETCH curObl1 INTO Obldoctype, Obldocno, Oblrefdocno, Obldollamt;
FETCH curRec2 INTO Recdoctype, Recdocno, Recrefdocno, Recdollamt;
IF NOT done THEN
IF obldocno = recdocno and Oblrefdocno = Recrefdocno and Obldollamt = Recdollamt THEN
INSERT into ar_no_ins_outs_delete (doc_type,doc_no,ref_doc_no,gv_dollar_amt)
values (Obldoctype, Obldocno, Oblrefdocno, Obldollamt);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE curObl1;
CLOSE curRec2;
END$$
DELIMITER ;
Thanks,
Melissa