MySQL stored procedures: all ways to produce an output

Last Updated on

A small monument in memory of the Eyemouth disaster
Sadly, not everything “returns”. This is a tribute to the fishers
who died in the sea of St Abbs, Scottish Borders, and their families.

There are many non-obvious ways to return values from MySQL stored procedures, and each of them solves particular problems.

MySQL stored procedures lack many features that you can find in PostgreSQL and in proprietary DBMSs. However, we’re going to discuss ways to work around its missing features.

RETURN

Stored functions have, of course, a RETURN statement to return values to the caller. I would argue that this should be the only method to return values from functions. Otherwise, we call function something that – conceptually – is not a function.

But if you don’t want to follow that advice, at least follow this: don’t tell Scala or Haskell programmers what you are doing. They could hurt you (here’s why).

OUT and INOUT parameters

OUT parameters are variables passed to a procedure (but not a function!), that can be populated in the body of the procedure itself, and then read after the procedure ended. INOUT parameters are variables used for both input and output. In this way procedures can return values just like functions, without the limitations of functions (like the inability to run a prepared statement). Even more, procedures can return multiple values. The only limitation is that the number of returned values is fixed.

Example:

CREATE PROCEDURE test(OUT p1 VARCHAR(100), OUT p2 VARCHAR(100))
BEGIN
    SET p1 := 'Hello';
    SET p2 := 'planet';
END

mysql> CALL test(@a, @b);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @a AS a, @b AS b;
+-------+--------+
| a     | b      |
+-------+--------+
| Hello | planet |
+-------+--------+
1 row in set (0.00 sec)

User variables

This method is the dirty version of the previous one. User variables exist at session level, and can be accessed from any context. As a consequence, any procedure is able to set any user variable, and that variable can be read from any other procedure or outside of all procedures. This method allows to “return” a variable set of values.

Example:

CREATE PROCEDURE test()
BEGIN
    IF @a = 1 THEN
        SET @a := 'Hello';
        SET @b := 'planet';
    ELSE
        SET @a := 'Hello planet';
    END IF;
END

mysql> SET @a := 1;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL test;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a AS a, @b AS b;
+-------+--------+
| a     | b      |
+-------+--------+
| Hello | planet |
+-------+--------+
1 row in set (0.00 sec)

Since MySQL has no native debugger for stored procedures, there is a temptation to use this method to track what happens during a procedure execution. But be careful: when doing this, it’s very easy to leave behind some dirty code. There are better ways to know what’s happening, see below.

Returning a JSON document

When you need to return multiple values without a fixed schema, JSON is the most logical choice. Not only it can be returned with any method, it can also be stored in tables. However, you will need a version of MySQL (8.0) or MariaDB (10.2) that supports JSON functions.

On this topic, see these previous articles:

Resultsets

Stored procedures (but not functions) can generate any number of resultset, with any schema. But these resultsets are only sent to the client. They cannot be returned or passed to another procedure, function or query.

Tables

To return multiple values with a clear schema, we can use a table – which is perfectly natural, in a relational database. These values will remain there, so they can be used by other procedures or queries without being sent to the client. For big resultsets, this can be a good optimisation, as I mentioned in 3 good reasons to use stored procedures.

It’s worth noting that we can populate any number of tables in a single procedure, “returning” different data structures. It’s also worth noting that the tables can be created by the procedure itself. So the schema of the data can actually be decided by the procedure itself, based on its logic. Metadata about the created tables are written in the information_schema database. Everything is allowed, as long as other procedures or scripts know how to read the results.

A table can also be used as a stored procedures log.

Temporary tables

If data is only going to be used in the current session, we can use temporary tables instead. This is cleaner, as other sessions will not see our data. But there are a couple of caveats:

  • For in-memory tables, we need to keep in mind that temporary tables must not be too big. Especially if many users can build their own temporary tables.
  • If the connection is interrupted, temporary tables and all their contents are lost.

Writing into a temporary table is a good way to store debug messages.

Prepared statements

Some DBMSs allow to return a resultset, or a cursor. MySQL doesn’t allow this. Writing data into a table is not the same thing – it would copy rows, and would have performance consequences.

EDIT: The previous paragraph is not clear/accurate. MySQL allows to return any number of resultsets. But only to the client. You cannot return a resultset or a cursor to another stored procedure or query.

A possible workaround is to create a prepared statement which is considered as a return value. Prepared statements, like user variables, exist at a session level, and can be prepared or executed or deallocated from any procedure. So a procedure could allocate a prepared statement that returns the desired result; and other procedures and programs will expect the prepared statement with a certain name to be the resultset returned by a certain procedure.

Note that a procedure can set any number of prepared statements. However, functions cannot.

EXECUTE IMMEDIATE

MariaDB supports the EXECUTE IMMEDIATE statement. It simply runs a prepared statements from any string. Therefore, in MariaDB a function or a procedure can return an SQL string with any of the previous methods, and it can be executed by other procedures.

An obvious advantage is that this string doesn’t need to be global or have a name that could conflict with something that already exists.

Views

The prepared statements method has two limitations:

  • Prepared statements are not accessible by other sessions.
    • If the connection breaks, prepared statements are lost.
  • Cursors cannot look over a prepared statement’s results.

Views exist for all sessions (PostgreSQL supports temporary views, but MySQL doesn’t). A cursor can easily be written to loop over a view:

DECLARE my_cursor CURSOR FOR
    SELECT * FROM your_db.her_view;

Stored procedures (but not functions) are free to create or drop views. A view can be considered as the result of a procedure.

Clean code

To keep the code clean-ish, we should avoid to use generic names for something that exists at session level. We should have a clear styleguide to follow. A possible pattern for these names is the following:

r_<procedure_name>_<result_name>

Where:

  • r_ stands for “result”;
  • adding the procedure name is a first good way to avoid conflicts;
  • but each procedure could return several results of the same type (ie, prepared statements), so we need to distinguish them with a further name.

We don’t need to distinguish, for example, a prepared statement from a temporary table, because their names cannot conflict.

It is important to note that prepared statements don’t work well with recursive procedure. A procedure can allocate a prepares statement and then call itself. But then, if it tries to allocate a prepared statement with the same name, it will generate an error.

MariaDB’s EXECUTE IMMEDIATE solves this problem.

References

Some of the mentioned features are pretty obscure for several people. Here are the links to the relevant pages of MySQL and MariaDB documentation.

MySQL manual

MariaDB knowledgebase

Relevant pages about features that somehow differ from MySQL:

See also

Related articles:

Related courses:

Conclusions

In this article I listed all ways I can think to return values from a procedure or a function. Each of them could have some use cases, and some of them are non-obvious ways to workaround some missing features.

Do you have some caveats to point to? Do you know more ways to return values? Did you ever use some “weird” way to return values, and why? Or, more simply – do you have questions?

As usual, all your comments are jolly welcome, and I’ll be looking forward to read them. Thanks to my readers who occasionally report my mistakes or force me to think from a different perspective.

Toodle pip,
Federico

Photo credit

Comments (4)

  1. Thank you for this write-up, very useful as usual!

    > Some DBMSs allow to return a resultset, or a cursor. MySQL doesn’t allow this.

    This is not my experience – in fact I use this method extensively in MariaDB when calling stored procedures from Python using the mysql.connector package. Simply define a stored procedure similar to this:

    CREATE PROCEDURE p1()
    BEGIN
    SELECT ‘hello world’;
    END

    … then call the procedure using `cursor.callproc(procname=procname, args=args)` and then pick up the result set from `cursor.stored_results()`.

    1. I meant `procname=’p1′, args=[]`. The args is a list of your stored procedure arguments, which is empty in my p1 example. Also, a stored procedure can return not only one, but multiple result sets if you want (just use multiple SELECTs in the stored procedure body), so `cursor.stored_results()` will actually return a list of result sets. Functions on the other hand can’t return result sets.

      1. Hi Karl!
        Yes, you are correct. But I should clarify what I meant: a stored procedure in MySQL can only return resultsets… to the client. Not to other stored procedures or queries. In some cases, this is annoying.

      2. I added a “resultsets” paragraph and an “EDIT”. Now it should be accurate and clearer. Thanks for reporting my mistakes, as usual!

Leave a Reply

Your email address will not be published. Required fields are marked *