5 messages in com.mysql.lists.win32RE: Stored Procedure
FromSent OnAttachments
Melissa Dougherty15 Jun 2006 12:15 
jbon...@sola.com.au18 Jun 2006 16:06 
Melissa Dougherty19 Jun 2006 04:41 
Melissa Dougherty19 Jun 2006 05:42 
jbon...@sola.com.au19 Jun 2006 16:00 
Subject:RE: Stored Procedure
From:jbon...@sola.com.au (jbon@sola.com.au)
Date:06/18/2006 04:06:10 PM
List:com.mysql.lists.win32

I think what you are trying to do boils down to this

INSERT INTO ar_no_ins_outs_delete (doc_type,doc_no,ref_doc_no,gv_dollar_amt) SELECT t1.doc_type, t1.doc_no, t1.ref_doc_no, concat('-',t1.gv_dollar_amt) FROM test.ar_no_ins_outs_temp AS t1, test.ar_no_ins_outs_temp AS t2 WHERE t1.doc-type in ('PV','GV') AND t2.doc_type in ('RG','RC','RT') AND t1.doc_type = t2.doc_no AND t1.ref_doc_no = t2.ref_doc_no AND concat('-',t1.gv_dollar_amt) = t2.gv_dollar_amt

It is always better to use joins than to use cursors. Cursors should always be a last resort. Joins are MUCH more efficient.

If gv_dollar_amt is a numeric type then you should replace concat('-',t1.gv_dollar_amt) with -gv_dollar_amt.

You should check that I have not mixed up any of the column names because I can't test this.

John B.

-----Original Message----- From: Melissa Dougherty [mailto:meli@cse-corp.com] Sent: Friday, 16 June 2006 4:46 AM To: win@lists.mysql.com Subject: Stored Procedure

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