Re: Stored Procedure

[Michael Smith]

Barry:

I have found some sample code and have used it to make error free code with MySQL Workbench as below.

Unfortunately it does not work in the environment I am in called Retool.

I will have to go back to their Forum Group and see if they cvan helpme.

Thanks for your help.

Mike
************

DROP PROCEDURE IF EXISTS pbackup1 ;

USE ccprocess;

DELIMITER //

CREATE PROCEDURE pbackup1()
BEGIN

DECLARE mContinue TINYTEXT DEFAULT " ";
DECLARE count1 INT DEFAULT 1;
DECLARE CCNT1 INT DEFAULT 1;

set mContinue = "YES";

WHILE mContinue = "YES" DO

set CCNT1 := (SELECT COUNT(Check01) FROM Invoices02 WHERE EXTRA1 = " " );

IF CCNT1 > 0 THEN
set @mInvnbr := (SELECT Invnbr FROM Invoices02 WHERE Check01 = 1 and EXTRA1 = " ");

UPDATE Invoices02 SET EXTRA1 = 'X' WHERE Invnbr = @mInvnbr;
ELSE
SET mContinue = 'NO';
END IF;
END WHILE;

END //

DELIMITER ;