Search code examples
mysqlstored-procedurescursor

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


Solution

  • 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.