MariaDB: Running a prepared statement in a stored procedure

How to run prepared statements in a MariaDB stored procedure. The article covers one-off prepared statements, reusable prepared statements, and parameters.

One-off prepared statements

We can run an SQL query from a text string.

EXECUTE IMMEDIATE 'DROP TABLE user';
EXECUTE IMMEDIATE CONCAT('DROP TABLE ', @table_name);
SET @sql := 'DROP TABLE user';
EXECUTE IMMEDIATE @sql;

Re-usable prepared statements

To prepare a statement, we need to use a session variable:

SET @sql := 'DELETE FROM user WHERE id = 5';
PREPARE stmt_delete_john_smith FROM @sql;

This is useful if we want to run this statement more than once. Actually, prepared statements exist at session level. A prepared statement defined in a procedure can be executed in another procedure, or outside any procedure. A procedure can even run a prepared statement without knowing exactly what it does.

Prepared statements created in a user connections are not visible by other user connections.

PREPARE will fail if a statement with that name was already prepared.

To run and destroy the prepared statement:

EXECUTE stmt_delete_john_smith;
DEALLOCATE PREPARE stmt_delete_john_smith;

You can use DROP PREPARE instead of DEALLOCATE PREPARE, if you wish. The effect is the same.

Prepared statements with parameters

In the previous example, we can repeat the same query many times, with not changes. But in reality, if we want to prepare a query to use it multiple times, we’ll usually want to use one or more parameters. In the previous example, the id of the user to delete can be a parameter:

SET @sql := 'DELETE FROM user WHERE id = ?';
PREPARE stmt_delete_user FROM @sql;

To call a prepared statement with parameters:

EXECUTE stmt_delete_user USING 5;

When using multiple parameters, we separate them with a comma. It’s possible to use any SQL expression.

EXECUTE some_stmt USING (10 + 5), CONCAT('a', 'b'), @var;

Compatibility

Oracle

MariaDB has an Oracle compatibility mode:

SET sql_mode := 'ORACLE';

In Oracle compatibility mode, it is possible to use Oracle syntax for parameters:

SET @sql := 'SELECT id FROM user WHERE name = :1 AND surname = :2';
PREPARE stmt_get_user FROM @sql USING 'Emma', 'Goldman';

MySQL

The syntaxes described in this article also work with MySQL, with some exceptions:

  • EXECUTE IMMEDIATE is not supported;
  • There is no Oracle compatibility mode.

Reference

MariaDB documentation: