MySQL Stored Procedure MySQL · Stored Procedure

MySQL Stored Procedure

Comprehensive introduction to MySQL stored procedures — what they are, why use them, the trade-offs (debugging difficulty, vendor lock-in, version control), procedures vs functions, the three parameter modes (IN/OUT/INOUT), procedure characteristics like DETERMINISTIC and SQL SECURITY DEFINER/INVOKER, recursion, and best practices.

What is a Stored Procedure?

A stored procedure is a named, parameterized block of SQL code that lives inside the database server itself. Once created, it can be called by name from applications, other procedures, triggers, or directly from a client — like calling a function in a regular programming language. The procedure body can contain any combination of queries, control flow (IF, CASE, LOOP, WHILE), variable declarations, transactions, and error handling.

Stored procedures became part of MySQL in version 5.0 (2005). Today every major relational database supports them — MySQL, MariaDB, PostgreSQL, SQL Server, Oracle — though the syntax differs from one to the next.

Why Use Them?

Benefit	What it means in practice
Reduce network traffic	Send one `CALL` instead of dozens of individual SQL statements. The application sends just the procedure name plus parameters.
Centralize business logic	Keep complex rules in the database where every application sees the same version. Avoids the "every app reimplements payroll" problem.
Granular security	Grant `EXECUTE` on the procedure without granting `SELECT`/`UPDATE` on the underlying tables. Apps call the procedure but can't touch the tables directly.
Reusability	Write once, call from anywhere — multiple apps, scheduled events, triggers.
Encapsulation	Procedure internals can change (table refactoring, index changes) without affecting calling code, as long as the parameter signature stays stable.

The Trade-Offs

Stored procedures are not a silver bullet. Practical drawbacks:

Debugging is painful. MySQL has no built-in debugger for stored procedures. You debug by adding SELECT 'reached step 3' AS dbg; statements — which is roughly where C programmers were in 1975.
Version control is awkward. The procedure body lives in the database, not in your Git repo by default. You need migration scripts to track changes properly.
Memory usage scales with connections. Each connection that uses a procedure caches its parsed form. Many connections × many procedures = real RAM cost.
Vendor lock-in. MySQL stored procedure syntax doesn't run on PostgreSQL or SQL Server. Moving databases means rewriting everything.
Fragmented logic. Some logic in the app, some in procedures — harder for new team members to find what runs where.

💡 The modern consensus: Use stored procedures for clear wins — bulk operations, security boundaries, complex transactional logic that genuinely benefits from being close to the data. Don't use them as a substitute for application-layer code just because the database can run code. The business logic in your app is easier to test, version, and debug.

Procedures vs Functions

MySQL has both stored procedures (this section) and stored functions. They look similar but differ in important ways:

Feature	Procedure	Function
Invoked with	`CALL proc_name(...)`	In any expression: `SELECT fn_name(...)`
Returns	Zero or more result sets via SELECT, plus OUT/INOUT parameters	Exactly one scalar value via `RETURN`
Parameter modes	IN, OUT, INOUT	IN only
Use in `WHERE` / `SELECT`	No	Yes
Can run DML (INSERT/UPDATE)	Yes	Yes — but with restrictions
Best for	Operations, transactions, multi-statement work	Reusable expressions and computations

Basic Syntax

DELIMITER $$ CREATE PROCEDURE procedure_name( [IN | OUT | INOUT] parameter_name datatype [, ... more parameters] ) [characteristic ...] BEGIN -- variable declarations -- SQL statements -- control flow (IF, LOOP, etc.) END $$ DELIMITER ;

Element	Purpose
`procedure_name`	The name you'll use to `CALL` it. Must be unique per database.
Parameter list	Zero or more parameters. Each has a mode (IN/OUT/INOUT — see below) and a data type.
`BEGIN ... END`	The procedure body. Can contain DECLAREs, SELECTs, control flow, transactions, anything.
`DELIMITER $$`	Temporarily changes the statement terminator so semicolons inside the body don't end the CREATE early. Restored at the end.
Characteristics	Optional metadata: `DETERMINISTIC`, `READS SQL DATA`, `SQL SECURITY DEFINER`, etc. (see below)

Parameter Modes

Every parameter has one of three modes that controls how data flows in and out:

Mode	Direction	Description
`IN` (default)	Caller → procedure	The procedure receives the value as a copy. Modifying it inside the body has no effect on the caller. This is how most parameters work.
`OUT`	Procedure → caller	The procedure writes a result into this parameter. The caller reads it after the call. The procedure cannot read the parameter's initial value — it starts as NULL inside the body.
`INOUT`	Both directions	The procedure reads the caller's value and writes back a new one. Useful for "increment this counter" or "transform this value" semantics.

⚠️ MySQL doesn't support default parameter values. Unlike Oracle PL/SQL or PostgreSQL, you can't write CREATE PROCEDURE foo(IN x INT DEFAULT 10). Every caller must supply every argument. This trips up developers coming from other databases.

Procedure Characteristics

Optional metadata between the parameter list and BEGIN tells MySQL how the procedure behaves. The most useful ones:

Characteristic	Meaning
`DETERMINISTIC` / `NOT DETERMINISTIC`	Same inputs always produce same outputs, or not. Defaults to NOT DETERMINISTIC. Affects replication and query caching.
`CONTAINS SQL` (default)	Procedure contains SQL but doesn't read or write tables.
`NO SQL`	Procedure contains no SQL statements at all.
`READS SQL DATA`	Procedure only reads tables (SELECTs), doesn't modify them.
`MODIFIES SQL DATA`	Procedure does INSERT, UPDATE, DELETE — can modify the database state.
`SQL SECURITY DEFINER` (default)	Procedure runs with the privileges of the user who created it.
`SQL SECURITY INVOKER`	Procedure runs with the privileges of the user who called it.
`COMMENT 'text'`	Free-text description, visible via `SHOW CREATE PROCEDURE`.

💡 SQL SECURITY is the security boundary. The default DEFINER mode means a procedure created by the DBA can do things the calling user couldn't do directly — exactly the granular-security benefit listed earlier. Use INVOKER mode when you specifically want the procedure to respect the caller's permissions.

Recursion

A procedure that calls itself is called recursive. MySQL supports recursion but disables it by default — you have to opt in by raising the depth limit:

MySQL — Enable recursion

-- 0 (the default) means recursion is disabled
SET @@max_sp_recursion_depth = 50;

Even when enabled, deep recursion is risky — each level consumes server memory. For tree traversals and similar work, recursive CTEs (WITH RECURSIVE, MySQL 8.0+) are usually the better choice.

Best Practices

Keep them focused. One clear job per procedure. Long procedures full of unrelated logic are hard to maintain.
Use descriptive names. transfer_funds, not sp1. Stored procedures live for years.
Always declare characteristics. Mark DETERMINISTIC when applicable, READS SQL DATA vs MODIFIES SQL DATA, etc. Helps the optimizer and other developers reading the code.
Wrap multi-statement work in transactions with proper error handling — the DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; pattern.
Use DROP PROCEDURE IF EXISTS at the top of deployment scripts so they can be re-run safely.
Don't over-use stored procedures. Application code is easier to test, version, and debug. Reach for procedures for clear wins.
Document parameters with COMMENT. The procedure name and parameter list aren't always self-explanatory.

What's Next

The pages that follow walk through the practical mechanics:

Creating Stored Procedures — the CREATE PROCEDURE statement, CALL, and DROP PROCEDURE
ALTER Procedure — modifying characteristics; the drop-and-recreate idiom for body changes
Listing Stored Procedures — finding what's installed using SHOW PROCEDURE STATUS and information_schema
Parameters — IN, OUT, and INOUT in worked examples
Variables — local variables, user variables, system variables; the three flavors and when each is appropriate

Conclusion

A stored procedure is a named SQL block stored in the database, invoked with CALL procedure_name(args).
Use them to reduce network traffic, centralize business logic, enforce security boundaries, and encapsulate complex transactional work — but be aware they're harder to debug, version, and port between database vendors.
Procedures differ from functions: procedures use CALL and can return result sets and OUT parameters; functions use RETURN and yield exactly one scalar value usable in any expression.
Parameters have three modes: IN (caller → procedure, default), OUT (procedure → caller), INOUT (both).
MySQL doesn't support default parameter values — every argument must be supplied at every call.
Optional characteristics like DETERMINISTIC, READS SQL DATA, and SQL SECURITY DEFINER/INVOKER declare the procedure's behavior and security context.