Dolt is a version controlled database, think if Git and MySQL had a baby. Our goal is to have Dolt be a drop-in replacement to MySQL; this includes support for Stored Procedures.

A Brief History

Stored procedures were initially introduced back in early 2021. At this point, users could write SQL queries within a BEGIN/END block, along with fundamental control flow in the form of IF/ELSE statements.

Over a year later, we significantly improved our Stored Procedures support. Here, we added support for local variables, cursors, loops, and even some handler support.

As time went on and our user base expanded, customers wanted to take advantage of more Stored Procedure features. More specifically, customers wanted to be able to run DDL statements within a procedure. Now, over two years later, we have made this requirement a reality.

Previously, Dolt would parse and run the entire procedure as a whole; this meant that parsing, analysis, and execution would occur over the entire procedure. This approach had its flaws, especially with DDL statements. For example, let's say we have a procedure defined like so:

create procedure proc()
begin
  create temporary table t (i int primary key);
  insert into t values (1), (2), (3);
  select * from t;
end;

In the old version, this would throw an Unresolved Table Error as the CREATE TABLE statement would be analyzed, but not executed, so the SELECT statement wouldn't see a created table. This incompatibility with MySQL Stored Procedures meant that we had work to do.

Our solution involved an entire rewrite of how Stored Procedures worked. Now, procedures are broken down into OpCodes, which are run within an Interpreter. This Interpreter maintains variables and the control flow. In other words, it's like the queries are run indiviually one after another. This approach is actually very similar to the how FUNCTIONs are implemented in Doltgresql. With these changes, the above procedure now correctly outputs:

tmp/main*> call proc();
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

Multiple Result Sets

While this recent rewrite brings us closer towards full MySQL compatibility, there are still missing features. One notable feature on our radar is missing support for multiple result sets. In MySQL, a Stored Procedure can contain mutiple select statements that each produce their own distinct result set. When a procedure in Dolt contains multiple SELECT statements, Dolt will execute each of them, but only retrieve the result set from the last SELECT statement.

Conclusion

The new and improved Stored Procedures are available starting at Dolt v1.53.0. Give them a shot, and if you find a bug don't hesitate to cut an issue on GitHub. Alternatively, chat with us on Discord.