The Lack of Flexibility of Stored Procedures in MySQL

Over three years ago I wrote about how you cannot use a stored procedure in a subquery. Well, it’s 2010, and I’m still annoyed by this and a handful of other things.

I was just working today on a report consisting of a series of queries, taking about a minute to generate. Some of the data would be created in a temporary table and queried against multiple times for performance reasons, and ultimately spit out into a CSV file for someone to examine later. I also would like to be able to return the result set, and perform queries on it, which is much faster than querying a view.

Fortunately, MySQL’s awesome SELECT … INTO OUTFILE can easily write CSV files to disk, so I’m covered there.

So when I started working on this, I was thinking this would be a great opportunity to demonstrate the flexibility and usefulness of stored procedures – after all, we will likely be needing this report again. However, there’s an issue. You can’t return a result set from a stored proc that you can actually do anything with. Everything gets spit directly back to the client. So, what are the alternatives?

Perhaps generate the data into a new table and swap with the old via a RENAME TABLE (if it exists)? Maybe not a bad solution. It works (see example 1 below), but now we’ve coupled the dataset to a global copy of the table, and any references we have to it are now hard coded, rather than passing back a workable dataset we can do anything we want with. Furthermore, if we want to make any changes to the dataset (UPDATE/DELETE) we are unable to – we must make a copy of the table and work from that, unless we don’t mind messing with someone work.

Temporary tables created in a stored procedure for the rest of the connection, so they can’t be used either (example 2), which is somewhat contrary to the manual.

Temporary tables are available past the execution of a stored procedure, contrary to what I wrote earlier. (example 2)

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.

Bummer. I’m assuming the stored procedure runs on its’ own isolated connection, which does make sense from a garbage collection point of view.

What about returning the data through the useful OUT parameter? Nope. Since I can’t store more than 1 row in a variable, that’s not happening either (example 3)

You also can’t generate the data in a new table and pass back the table name in an OUT variable. (example 4)

In conclusion: of the above methods, it seems the most likely route I’ll follow in the future will be to store the results in some permanent table, and run the proc off hours to regenerate the table, hoping that no one’s using it. It would need to be read only – and working with it would require creating your own copy of the table. It has the advantage of being easily understood – you can quickly DESCRIBE the columns and know what you’re getting back – so I suppose it’s going to have to be good enough. But what I really wanted was

mysql> call some_proc() into outfile "/tmp/myfile.txt";

For more information on stored procedures, check out the Stored procedure syntax.

Example 1: Swapping tables within a stored procedure

drop procedure test_proc;
delimiter //

CREATE procedure test_proc ()
begin
create table jon_new LIKE jon;
rename table jon to jon_old, jon_new to jon;
drop table jon_old;
end;
//
delimiter ;

mysql> show tables;
+------------------+
| Tables_in_test   |
+------------------+
| jon              |
+------------------+
4 rows in set (0.00 sec)

mysql> insert into jon values (1);
Query OK, 1 row affected (0.04 sec)

mysql> select * from jon;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> call test_proc();
Query OK, 0 rows affected (1.53 sec)

mysql> select * from jon;
Empty set (0.00 sec)

Example 2: Trying to create a temporary table in a stored procedure (updated)

mysql> delimiter //
mysql> create procedure test_proc3()
    -> begin
    -> CREATE TEMPORARY TABLE my_table (id serial);
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
mysql> call test_proc3();
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> select * from my_table;
Empty set (0.00 sec)
t

Example 3: Trying to store multiple rows in a variable

mysql> create table my_table (id int primary key);
Query OK, 0 rows affected (1.46 sec)

mysql> set @v = (select * from my_table);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into my_table values (1),(2),(3);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> set @v = (select * from my_table);
ERROR 1242 (21000): Subquery returns more than 1 row

Example 4: Trying to select from a table where the name was defined in a variable

mysql> set @tab = "my_table";
Query OK, 0 rows affected (0.00 sec)

mysql> select @tab;
+----------+
| @tab     |
+----------+
| my_table |
+----------+
1 row in set (0.00 sec)

mysql> select * from @tab;
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your
MySQL server version for the right syntax to use near '@tab' at line 1