Stored procedure does not work in retool

App Building
1

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

I have included a screen capture as well.

Any thoughts?

Mike

pict1
pict1502×776 66.2 KB

2

I think @Kabirdas solved this for you in this post

3

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.

Mike

4

Hi @mdsmith1

I believe you need to use @ symbols in front of the mresult & mresult2 variables

:crossed_fingers: hope that helps!