asked on

mysql stored procedure resultset limit - how do I get more?

I have a stored procedure which populates a temporary table which I then successively query on different criteria to return different resultsets in the stored procedure. Everything is working --except that-- I can only see a maximum of 10 resultsets being returned from the stored procedure when there actually are more than 10.

Here is a dummy stored procedure (not my real one which is more complex) which should give 11 resultsets but which only gives 10 -- with number 11 being "dropped":

BEGIN
select 1 as result;
select 2 as result;
select 3 as result;
select 4 as result;
select 5 as result;
select 6 as result;
select 7 as result;
select 8 as result;
select 9 as result;
select 10 as result;
select 11 as result;
END

Open in new window

If I change the order of things, like:

BEGIN
select 1 as result;
select 2 as result;
select 3 as result;
select 4 as result;
select 5 as result;
select 6 as result;
select 7 as result;
select 8 as result;
select 9 as result;
select 11 as result;
select 10 as result;
END

Open in new window

I now see result 11 but then result 10 goes "missing" --so I am still limited to a total of 10 resultsets.

Is this a mysql thing which I can override (and if so, how)?

In case it matters, I'm creating and viewing the results of the stored procedure using Navicat for Mysql software. I don't know if this is the cause of the maximum 10 limitation or not.

johanntagle

Works for me:

mysql> delimiter $$
mysql> create procedure shanghai()
    -> BEGIN
    -> select 1 as result;
    -> select 2 as result;
    -> select 3 as result;
    -> select 4 as result;
    -> select 5 as result;
    -> select 6 as result;
    -> select 7 as result;
    -> select 8 as result;
    -> select 9 as result;
    -> select 10 as result;
    -> select 11 as result;
    -> END;
    -> $$
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter ;
mysql> call shanghai();
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      7 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      8 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|      9 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|     10 |
+--------+
1 row in set (0.00 sec)

+--------+
| result |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Open in new window

ShanghaiD

ASKER

Thanks for the quick reply.

I'll look into things a little more before closing the question out (and awarding points).

It seems it may be a Navicat GUI things -- where the resultsets are in fact all being returned but the Navicat front end is limiting its views to a maximum of 10.

I'll double check what is actually returned to my browser with Firebug and post an update.

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ShanghaiD

ASKER

Of course! I just did -- and it shows all 11 resultsets -- so it looks like it is a Navicat GUI limitation (which I can live with).