Stored Procedures

[Michael Smith]

I am still learning how to do Stored Procedures.

In this case, I have an Invoice Master File where each Invoice has about 5 rows. There are maybe 100 invoices in the file and just the first row of each invoice is shown on screen.
The operator can click on several invoices to mark them for deletion.

I first send the master file to the server with the check marks. I have developed the stored procedure below using a 3rd party tool.

DELIMITER $$
CREATE FUNCTION DeleteChkInv ( )
RETURNS int;
SET xinv1 = 0;
WHILE xinv1 = 0
BEGIN
SELECT @minv := Invnbr FROM Invoices02 WHERE Check01 = 1;
@v1 := SELECT FOUND_ROWS();
if @v1 > 0
DELETE FROM Invoices02 WHERE Invnbr = @minv;
else
xinv1 = 1
endif
END; $$
DELIMITER ;

I am trying to create a WHILE Loop that looks for the first Check Mark (Check01 = 1). As it finds each Checkmark, it deletes all the Invoice Rows with the Invoice Number on the Check Mark Row.

It continues with this until it can find no more checkmarks and sets xinv = 1 to end the loop.

This has so many error in it that I can make no sense of the debugger.

Maybe if you could find 1 error, I can start with that and see if the debugger will become more helpful.

Mike