Mariadb Create Procedure

CREATE PROCEDURE in MariaDB

Last updated: 2026-07-20

This skill covers the delta between what an LLM tends to write for a "stored procedure" and MariaDB's actual CREATE PROCEDURE. It assumes the agent already knows the general concept (parameters, a procedural body, CALL to invoke). Everything below documents MariaDB-specific clauses, privilege behavior, parameter semantics, and the most common LLM traps.

Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.

What LLMs Often Miss

Syntax

CREATE
    [OR REPLACE]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ OUT | INOUT | IN OUT ] param_name type |
    [ IN ] param_name type [DEFAULT value_or_expression]

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

OR REPLACE and IF NOT EXISTS are mutually exclusive. IN OUT (as two words) and the NOCOPY parameter hint are Oracle-mode-only syntax; default-mode MariaDB uses INOUT (one word). Parameter DEFAULT values/expressions are available on any parameter regardless of mode (since 11.8).

Parameter Modes

• IN (default when omitted) — passes a value in; the caller never sees changes made inside the procedure.
• OUT — starts as NULL inside the procedure; its final value is visible to the caller. Must be called with a variable (@v, or a routine-local variable/parameter when called from another routine).
• INOUT — caller-supplied initial value, modifiable, changes visible on return.

CREATE PROCEDURE simpleproc (OUT param1 INT, IN threshold INT DEFAULT 100)
BEGIN
  SELECT COUNT(*) INTO param1 FROM t WHERE val > threshold;
END;

Characteristics

• LANGUAGE SQL — the only language currently supported; documentation-only clause.
• DETERMINISTIC / NOT DETERMINISTIC — no effect on procedures (functions only). Default NOT DETERMINISTIC.
• Data-access clauses (CONTAINS SQL / NO SQL / READS SQL DATA / MODIFIES SQL DATA) — advisory only, never validated against the body. Default CONTAINS SQL if none given.
• SQL SECURITY { DEFINER | INVOKER } — controls whose privileges are checked at call time. Default DEFINER. With DEFINER, the routine runs with the definer account's privileges regardless of who calls it; with INVOKER, the caller's own privileges are checked.
• COMMENT 'string' — free-text, shown in SHOW PROCEDURE STATUS / INFORMATION_SCHEMA.ROUTINES.

Privileges

• Creating a procedure requires CREATE ROUTINE.
• Specifying a DEFINER other than the calling account requires the SET USER privilege.
• The creator is auto-granted ALTER ROUTINE and EXECUTE on the new routine (governed by automatic_sp_privileges, default on).
• CREATE OR REPLACE PROCEDURE preserves existing routine-specific privileges; a plain DROP PROCEDURE + CREATE PROCEDURE does not.
• DROP PROCEDURE/ALTER PROCEDURE require ALTER ROUTINE on the routine.

Body and Invocation Notes

• routine_body is any valid SQL procedure statement — a single statement, or a BEGIN ... END compound block with declarations, handlers, loops, etc.
• Unlike stored functions, procedures may contain transaction-control statements (COMMIT, START TRANSACTION, ...) and DDL (CREATE, DROP, ...).
• Invoke with CALL sp_name(...) — never SELECT.
• If the procedure name collides with a built-in SQL function name, a space is required between the name and ( both when defining and calling it.
• The procedure's effective sql_mode is captured at creation time and used on every execution afterward, independent of the session's sql_mode when it's called.
• Oracle-mode PL/SQL-style procedure bodies are supported under sql_mode=ORACLE (a subset of PL/SQL syntax, including IN OUT and NOCOPY parameters) — a distinct body-syntax mode, not a separate statement.
• DELIMITER (e.g. DELIMITER //) is a client convenience (mariadb CLI only) for feeding a multi-statement body containing ; as one block — it is not server syntax and irrelevant when the CREATE PROCEDURE text is sent as a single prepared statement over the protocol.

See Also

• mariadb-create-function — stored functions: DETERMINISTIC actually matters, single RETURNS value, no transaction/DDL statements
• mariadb-create-trigger — trigger-specific FOR EACH ROW, timing, and OLD/NEW row access
• mariadb-call — invoking procedures, passing OUT/INOUT arguments
• Canonical references on mariadb.com/docs (consult only for edge cases not covered here):
  • https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures/create-procedure
  • https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures/stored-procedure-overview
  • https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures/alter-procedure
  • https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions/stored-routine-privileges

This page is: Copyright © 2026 MariaDB. All rights reserved.