CODEWITHSUNDEEP
mysqlstored-procedurescursor
user1028405
user1028405

Reputation: 33

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

Upvotes: 3

Views: 4841

Answers (2)

aeroson
aeroson

Reputation: 1129

Here is how I did it:

Have the procedure return its resultset inside temporary table.

create procedure GetPeople(in unitId bigint(20))
begin

    create temporary table if not exists GetPeople_result like People;
    truncate table GetPeople_result;

    insert into GetPeople_result select * from
        -- your select here
    ;
end

Make cursor for the temporary table.

create procedure DoSomethingWithAllPeople(in unitId bigint(20))
begin

    declare allPeopleCursor cursor for select PersonId from GetPeople_result; -- select the result from GetPeople
    call GetPeople(unitId); -- create the GetPeople_result table and fill it

    -- iterate cursor

end

Upvotes: 1

Johan
Johan

Reputation: 76547

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.

Upvotes: 3

Related Questions