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
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.