Mysql stored procedure

Question

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

Answer 1

• http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html

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:

• http://www.databasedesign-resource.com/mysql-stored-procedures.html

Answer 2

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