Stored Procedures, Parameters and Flow Control

1. Stored Programs

A stored procedure is a named program stored in the database and invoked with CALL. It can accept parameters, declare local variables, execute SQL, use conditional and loop statements, return result sets and coordinate transactional work.

Stored procedures are useful for operations that belong close to shared data: controlled updates, data validation, administrative processing and a stable database API for several clients.

They are not automatically faster or easier to maintain than application code. Version control, deployment, testing, observability and portability must be planned.

2. Creating a Procedure

DELIMITER //

CREATE PROCEDURE count_products(OUT p_total BIGINT)
BEGIN
    SELECT COUNT(*)
    INTO p_total
    FROM product;
END//

DELIMITER ;

CALL count_products(@total);
SELECT @total;

The procedure assigns the row count to its OUT parameter. The caller supplies a session variable beginning with @ to receive the value.

3. The Client Delimiter

Statements inside a stored-program body end with semicolons. An interactive client that normally sends text at the first semicolon would split the CREATE PROCEDURE definition prematurely.

DELIMITER changes which token that client recognizes as the end of the complete definition. It is a client command, not part of the stored procedure and not a server-side transaction statement.

Application drivers can usually send the complete definition through their APIs without embedding an interactive delimiter command.

4. Calling and Removing Procedures

CALL invokes a procedure:

CALL find_customer_orders(42);

A procedure may return zero or more result sets in addition to OUT or INOUT values. Client code must consume all results according to its database driver.

DROP PROCEDURE removes a definition:

DROP PROCEDURE IF EXISTS find_customer_orders;

CREATE OR REPLACE support varies by stored-object type and MySQL version, so deployments often use explicit drop-and-create migrations.

5. IN Parameters

IN is the default parameter mode and supplies a value to the procedure:

CREATE PROCEDURE list_orders(IN p_customer_id BIGINT)
BEGIN
    SELECT order_id, ordered_at, total_amount
    FROM order_header
    WHERE customer_id = p_customer_id
    ORDER BY ordered_at DESC, order_id DESC;
END;

Assignments inside the procedure do not return a changed IN value to the caller. Prefixing parameters with p_ helps distinguish them from columns.

6. OUT Parameters

An OUT parameter carries a result back:

CREATE PROCEDURE customer_order_count(
    IN p_customer_id BIGINT,
    OUT p_order_count BIGINT
)
BEGIN
    SELECT COUNT(*)
    INTO p_order_count
    FROM order_header
    WHERE customer_id = p_customer_id;
END;

The caller provides a variable capable of receiving the output. Its incoming value is not the procedure's input contract.

OUT parameters suit small fixed result sets. A SELECT result set is often clearer for tabular output.

7. INOUT Parameters

An INOUT parameter carries an initial value into the routine and a final value out:

CREATE PROCEDURE add_tax(
    INOUT p_amount DECIMAL(12, 2),
    IN p_rate DECIMAL(6, 4)
)
BEGIN
    SET p_amount = ROUND(p_amount * (1 + p_rate), 2);
END;

The caller initializes a session variable, passes it to CALL and reads the changed value afterward.

Use INOUT when modifying one logical caller value is natural. Separate IN and OUT parameters can be clearer when input and result have different meanings.

8. Compound Statements

BEGIN and END form a compound statement containing declarations and executable statements:

BEGIN
    DECLARE v_count BIGINT DEFAULT 0;
    SELECT COUNT(*) INTO v_count FROM product;
    SET p_total = v_count;
END

Nested blocks create nested scope. Local variables cease to exist when their block ends.

Declarations must appear before executable statements in a block and follow MySQL's required declaration ordering among variables, conditions, cursors and handlers.

9. Local and Session Variables

Local variables are declared with DECLARE and exist only within their block. Parameters are also local to the routine invocation.

Session user variables use @name and remain associated with the connection:

SET @customer_id = 42;
CALL customer_order_count(@customer_id, @count);

Connection pools can reuse sessions, so application logic should not assume session variables begin empty. Prefer parameterized calls and explicit initialization.

Local variables are usually safer for routine implementation because their scope and type are declared.

10. Assignment

SET performs direct assignment:

SET v_subtotal = v_quantity * v_price;

SELECT ... INTO assigns query columns:

SELECT customer_name, status
INTO v_name, v_status
FROM customer
WHERE customer_id = p_customer_id;

The query must have the expected cardinality. Multiple rows cause an error. No rows raises a not-found condition that can be handled or prevented through an aggregate or existence check.

11. Naming and Scope

Ambiguous names can silently refer to a parameter or column differently than intended:

WHERE customer_id = customer_id

This might become a tautology rather than a parameter comparison. Use conventions and aliases:

WHERE c.customer_id = p_customer_id

Qualify columns in multi-table statements. Clear naming prevents logic defects that remain syntactically valid.

12. IF Statements

IF p_amount < 0 THEN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Amount must be nonnegative';
ELSEIF p_amount = 0 THEN
    SET p_category = 'ZERO';
ELSE
    SET p_category = 'POSITIVE';
END IF;

IF uses THEN, optional ELSEIF branches, optional ELSE and END IF. Conditions follow SQL three-valued logic; UNKNOWN does not act as TRUE.

This procedural IF statement differs from MySQL's scalar IF() function.

13. CASE Statements

The simple CASE compares one expression:

CASE p_status
    WHEN 'N' THEN SET p_label = 'NEW';
    WHEN 'P' THEN SET p_label = 'PAID';
    ELSE SET p_label = 'OTHER';
END CASE;

The searched CASE evaluates separate predicates:

CASE
    WHEN p_total >= 10000 THEN SET p_band = 'HIGH';
    WHEN p_total >= 5000 THEN SET p_band = 'MEDIUM';
    ELSE SET p_band = 'STANDARD';
END CASE;

Procedural CASE controls statements. The CASE expression used inside SELECT returns a value.

14. WHILE

WHILE checks its condition before each iteration:

SET v_counter = 1;
WHILE v_counter <= p_limit DO
    INSERT INTO number_log(value) VALUES (v_counter);
    SET v_counter = v_counter + 1;
END WHILE;

The body can execute zero times. Every loop needs a clear progress step and termination condition.

Set-based SQL is usually preferable for transforming sets of rows. Use procedural loops when iterations truly depend on prior procedural state.

15. REPEAT

REPEAT executes its body and checks UNTIL afterward:

SET v_counter = 1;
REPEAT
    SET v_total = v_total + v_counter;
    SET v_counter = v_counter + 1;
UNTIL v_counter > p_limit
END REPEAT;

The body runs at least once. The UNTIL condition ends the loop when it becomes TRUE, which is the opposite wording from a WHILE continuation condition.

Consider behavior when the condition is NULL; an UNKNOWN termination condition does not supply TRUE.

16. LOOP, LEAVE and ITERATE

LOOP has no built-in condition:

process_loop: LOOP
    SET v_counter = v_counter + 1;

    IF v_counter > p_limit THEN
        LEAVE process_loop;
    END IF;

    IF MOD(v_counter, 2) = 0 THEN
        ITERATE process_loop;
    END IF;

    INSERT INTO odd_number(value) VALUES (v_counter);
END LOOP;

LEAVE exits the named loop or block. ITERATE skips to the next iteration of the named loop. Labels make nested control flow explicit.

17. Set-Based Operations Before Loops

This loop-oriented intention:

for every expired session, delete it

is naturally expressed as:

DELETE FROM session
WHERE expires_at < CURRENT_TIMESTAMP;

The set-based statement gives the optimizer freedom to select an access plan and avoids per-row statement overhead.

Loops remain useful for cursor-driven external-style sequencing or procedural algorithms that cannot be expressed clearly as relational operations. Their use should be justified by semantics rather than familiarity with general-purpose programming.

18. Transactions in Procedures

A procedure can execute START TRANSACTION, COMMIT and ROLLBACK where permitted, but transaction ownership must be explicit.

If a reusable procedure commits internally, a caller cannot combine it atomically with additional work. A procedure intended as one complete business command may own its transaction; a lower-level composable routine often should let the caller own it.

Document whether the routine begins, commits, rolls back or assumes an existing transaction. Remember that DDL can cause implicit commits.

19. Error Handling

Stored routines can declare handlers for SQL conditions. A transactional procedure often needs an exit handler that rolls back and rethrows:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    RESIGNAL;
END;

The handler must be declared before executable statements. Swallowing an error and returning apparent success leaves callers unable to distinguish completion from partial failure.

Detailed handlers, SIGNAL and RESIGNAL are developed with trigger and handler design, but every procedure contract should already define its failures.

20. Security Context

Stored objects execute under a SQL SECURITY context, commonly DEFINER or INVOKER as supported. A definer-security routine can expose a narrowly controlled operation without granting clients direct table privileges.

This is powerful and must be reviewed. Definer accounts, object ownership, dynamic SQL and input validation affect privilege boundaries.

Grant EXECUTE only to roles that need the routine. Avoid creating routines under an unnecessarily powerful permanent account.

21. Dynamic SQL

MySQL prepared statements can construct SQL dynamically when object names or query shape must vary. Data values should still use parameters rather than string concatenation.

Dynamic object identifiers cannot always be bound as ordinary values and need strict allow-list validation. Concatenating untrusted text creates SQL injection risk even inside a stored procedure.

Prefer static SQL when the schema objects are known. It is easier to validate, authorize, analyze and optimize.

22. Procedure Contracts

A useful procedure contract states:

• parameter names, modes, types and NULL rules;
• result sets and OUT values;
• modified tables and invariants;
• required privileges;
• transaction ownership and isolation expectations;
• errors and retryable conditions;
• idempotency behavior.

A procedure that sends external effects or creates nonrepeatable business identifiers needs an explicit retry design.

23. Deployment and Observability

Store routine definitions in version-controlled migration files. Deploy them in a known order with dependent views, grants and application releases.

Log calls at the appropriate application boundary, monitor duration and lock waits and inspect slow statements executed inside routines. A single CALL can hide several expensive statements from superficial application timing.

Test parameter boundaries, NULL inputs, missing rows, duplicate rows, concurrent calls, handler paths and privilege behavior.

24. Designing Stored Procedures

Use a procedure when centralized database behavior adds clear value. Keep it focused on one business responsibility. Prefer set-based statements, constrain parameters, use unambiguous names and handle every affected-row count.

Decide who owns the transaction before writing COMMIT. Propagate failures honestly. Limit privileges and document results.

Stored procedures work best as deliberate database interfaces. They become difficult when they mix unrelated workflows, hidden commits, broad privileges, loops over set operations and undocumented result sets.