Stored Procedures

[Michael Smith]

This is my first Stored Procedure.

I have a file Test01 with a list of Invoice Numbers. The Invoice File (Invoices02) has many invoices where each invoice could have 5 or more rows. Each group of rows for an invoice has a unique Invoice Number (invmbr).

The Test01 file starts with all rows for Field1 as blank. As each invoice number is read from Test01 then Field1 is marked as "X".

As each invoice number is read from the Test01 file, all rows in the Invoice file are marked as EXTRA1 = "X" for that Invoice Number.

I have developed a Stored Procedure as below to do this but it doesn't work. The error message is not meaningful. I would appreciate any guidance on this.

Mike

DROP PROCEDURE IF EXISTS markInv02;
DELIMITER $$
CREATE PROCEDURE markInv02()

BEGIN
DECLARE count1 INT DEFAULT 1;
WHILE count1 > 0
SELECT COUNT(invnbr) where Field1 = " " AS count1 FROM Test01;
IF count1 > 0
SELECT top invnbr WHERE Field1 = " " INTO @invValue FROM Test01;
UPDATE Test01 SET Field1 = "X" where invnbr = @invValue;
UPDATE Invoices02 SET EXTRA = "X" where invnbr = @invValue;
END IF;
END WHILE;
END$$

DELIMITER ;