Stored procedure returning values: bug, artefact, undocumented feature?

user209974 0
Viewed 25

I couldn't help noticing that stored procedures are able to return values:

create table foo(foo int);
insert into foo values (42);
create procedure get_foo() select * from foo;
call get_foo();
+------+
| foo  |
+------+
|   42 |
+------+

More intriguing, wrapper such as python's MySQLdb do pass through the procedure's output to the caller.

However, no such behavior is documented. The documented way to get results out of the procedure is using OUT procedure arguments. I find this return value of stored procedure hardly used or indeed mentioned anywhere, yet it seems to be a useful behavior.

My question is, is this an artifact of stored procedures, or is it implemented on purpose but undocumented for some reason? How long has it been so, and can one rely on it?

(I tested on MariaDB 10.3 and don't know how this extends to MySQL as well).

mariadb stored-procedures mysql
2 Answers

This is the way stored procedures are designed to work in MySQL, since they were introduced circa 2005.

https://dev.mysql.com/doc/refman/8.0/en/stored-routines-syntax.html

MySQL supports a very useful extension that enables the use of regular SELECT statements (that is, without using cursors or local variables) inside a stored procedure. The result set of such a query is simply sent directly to the client. Multiple SELECT statements generate multiple result sets, so the client must use a MySQL client library that supports multiple result sets.

I couldn't find a similar statement in the MariaDB documentation on stored procedures. I can't explain why they didn't choose to document this feature. As far as I know, they have not changed it since they forked from MySQL in 2010.

Bill Karwin0

Since the first implementation (afaik it was in MySQL 5.0, written by Per Erik Martin), stored procedures in MariaDB/MySQL were able to return result sets.

It is very well documented in both MySQL and MariaDB documentation which kind of SQL statements are not supported.

Please also note, that not only a SELECT but also an INSERT might return a result set.

Georg Richter0
Related