Re: Stored Procedure

I just remember another issue with declaring the cursors.... I want to use 
the doc_no from the first cursor as a variable in the second cursor.  That's 
where I'm having the issue.

Melissa

----- Original Message ----- 
From: <[email protected]>
To: <[email protected]>; <[email protected]>
Sent: Sunday, June 18, 2006 7:06 PM
Subject: 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]
>
> 


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