3

I have created a stored procedure in Mysql, as :

DELIMITER //
CREATE PROCEDURE test()
BEGIN
  SELECT *  FROM buyers;
END //
DELIMITER ;

but when i call it using,

call test()

it returns an error saying :

#1312 - PROCEDURE ticketninja.test1 can't return a result set in the given context

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Kalpesh Jain
  • 409
  • 3
  • 10
  • 19

2 Answers2

2

Statements that return a result set can be used within a stored procedcure but not within a stored function. This prohibition includes SELECT statements that do not have an INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE. For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs (ER_SP_NO_RETSET). For statements that can be determined only at runtime to return a result set, a PROCEDURE %s can't return a result set in the given context error occurs (ER_SP_BADSELECT).

Maybe you need to utilize a temporary table, like in this example:

miku
  • 181,842
  • 47
  • 306
  • 310
2

Make sure your code (or client library) calls mysql_set_server_options() with MYSQL_OPTION_MULTI_STATEMENTS_ON enabled.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614