Can't run a Stored Procedure with multiple SELECT queries
Posted by: Kumar Kush
Date: January 22, 2020 06:53PM
I have written several Stored Procedures which have multiple SELECT statements in order to return multiple ResultSets to the calling script. SQL script of one such procedure is as below:
CREATE PROCEDURE `GetLists` ()
SELECT CompanyID, CompanyName FROM companies ORDER BY CompanyName ASC;
SELECT ClauseID, Clause FROM excess_clause ORDER BY ClauseID ASC;
The procedure works perfectly on locally hosted MySQL on my computer.
But on a hired / remote server, running the script in phpMyAdmin is throwing an error as below:
1 errors were found during analysis.
Missing expression. (near "ON" at position 25)
SQL query: Edit Edit
SET FOREIGN_KEY_CHECKS = ON;
MySQL said: Documentation
#2014 - Commands out of sync; you can't run this command now
The PHP script and database clients like HeidiSQL are also throwing errors their way.
Just in case required, on my local machine:
MySQL version - 5.6.30-76.3
Server version - 10.1.16-MariaDB
while on the remote server:
MySQL version - 5.7.28
Server version - 10.2.30-MariaDB
Easier workaround is sending each SELECT query in the procedure individually from my PHP script, but that, obviously, is cumbersome and will be heavier on the application server.
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.