user1028405 user1028405 - 1 month ago 7
MySQL Question

Mysql: Procedure call within Cursor declaration

I am attempting to use a MySql stored procedure call as part of my cursor declaration (to nest procedures and be able to view results):

declare myCursor cursor for call MyProcedure(param1,param2,param3);


When I do this though, I get the 1064 error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use.

Anyone know how to accomplish this?

Thank You

Answer

http://dev.mysql.com/doc/refman/5.5/en/declare-cursor.html

Says:

DECLARE cursor_name CURSOR FOR select_statement

call is not a select_statement.
That's why you're getting an error.

Workaround
If you're using a stored proc that returns a resultset, use the equivalent select statement instead.

Problem
The problem is that a call can return 0, 1 or more resultsets.
The cursor can only handle the 1 resultset case and AFAIK MySQL has no way to know for sure how many resultsets the call will return.

Comments