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.
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.
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)
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.
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:
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.
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.
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.
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.
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.
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.
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_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.
EXECUTE IMMEDIATE solves this problem.
Some of the mentioned features are pretty obscure for several people. Here are the links to the relevant pages of MySQL and MariaDB documentation.
- CREATE PROCEDURE (for OUT and INOUT)
- User-defined variables
- JSON functions
- Temporary tables
- Prepared statements (SQL syntax)
Relevant pages about features that somehow differ from MySQL:
- Working with JSON arrays in MySQL/MariaDB
- Working with JSON objects in MySQL/MariaDB
- Variable number of parameters and optional parameters in MySQL/MariaDB procedures
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.