RE: Stored Procedure

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:[email protected]] 
Sent: Friday, 16 June 2006 4:46 AM
To: [email protected]
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 

-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]