IF ELSE

[Michael Smith]

I am trying to deal with cases where a new invoice is building but it needs an invoice number.

I have stored the next invoice number in a file called Storage01 in the field Nextra5.

The code below deals with this and is accepted by MySQL Workbench but when I try to run it I get an error "Not allowed to return a result set from a function". But if I remove the Function, I get other errors.

Any suggestions would be appreciated.


DELIMITER //
CREATE FUNCTION COUNT_ITEM (NINVS INT)
RETURNS INT

BEGIN

SELECT count(ITEM) FROM Invoices01 as NINVS where invnbr > 0 ;
IF NINVS = 0 THEN
SELECT @mnewnumber := Nextra5 from Storage01 ;
update Invoices01 set invnbr = @mnewnumber;
END IF;

END; //

DELIMITER ;