MySQL: create stored procedure and call it immediately

129
January 11, 2019, at 00:10 AM

I need to create a stored procedure then immediately call it, all within the same .sql file. The procedure is simple enough and just calculates the difference between income and outgoings. The trouble is, when I call the procedure I get this error message

the procedure is as follows:

DELIMITER $$            
    CREATE PROCEDURE crs.CurrentFinanceReport()
    BEGIN   
        DECLARE  income DECIMAL(6,2) default 0; 
        DECLARE  outgoings DECIMAL(6,2) default 0; 
        SET income = (SELECT SUM(invoice.TotalPrice) FROM crs.invoice INNER JOIN crs.orders ON orders.CustomerEmail = invoice.CustomerEmail WHERE orders.OrderStatus != "Recieved");
        SET outgoings =(SELECT SUM(OrderCost) FROM crs.stockOrder WHERE DateTimeRecieved = 0);
        SELECT (income - outgoings);
    END $$
    DELIMITER ;

And I am calling it with this:

CALL crs.CurrentFinanceReport();

If it helps at all, I have found that if the procedure is empty (nothing between BEGIN and END) it can call it without an error, but as soon as I add even a simple SELECT * it causes the error.

Answer 1

SET FORIEGN_KEY_CHECKS=ON is a query generated by phpmyadmin and not your code. Use a mysql client and you probably won't encounter this error (or any others due to phpmyadmin "cleverness").

Rent Charter Buses Company
READ ALSO
MySQL grouping different rows to create group and simple products

MySQL grouping different rows to create group and simple products

I am migrating products from one system to Magento and there is a group/child products relation in an old system and I have to maintain in the Magento System

129
Mysql Join and Case Issues

Mysql Join and Case Issues

I have the following Two tables

149
how to extract json array as table from mysql text column when number of json objects in array is unknown?

how to extract json array as table from mysql text column when number of json objects in array is unknown?

Is there a way to extract data from a text column containing json arrays with a varying number of json objects into a table?

187