I have a text book Stored Procedure which works fine in MySQL Workbench.
DELIMITER //
CREATE PROCEDURE GetAllInvoices2()
BEGIN
SELECT * FROM Invoices02;
END //
DELIMITER ;
But when I delete the procedure in Cpanel and run it again in Retool I get the error message below:
Test6 failed (0.362s):You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER // CREATE PROCEDURE GetAllInvoices2() BEGIN SELECT * FROM Invoices0' at line 1
Scott, the code I came up with following Henry's model is below. I used MySQL workbench for the writeup.
DELIMITER $$
DROP PROCEDURE IF EXISTS GetNew;
CREATE PROCEDURE GetNew(
INOUT mresult INT ,
OUT mresult2 INT)
BEGIN
DECLARE totnew INT;
SELECT COUNT(*) INTO totnew FROM Invoices02 WHERE Invnbr = 728;
IF totnew > 0 THEN
SET mresult = 0;
ELSE
SET mresult = 1;
END IF;
SET mresult2 = mresult;
END$$
DELIMITER ;
I have then tried to call the procedure with:
CALL GetNew(mresult2);
SELECT mresult2;
This gives the result:
Error Code: 1054. Unknown column 'mresult2' in 'field list'
I get the idea of how the stored procedure works. I just don't know how to call it.