MySQL stored procedures explained with WordPress database examples

A MySQL stored procedure is a named block of SQL code stored in the database that you can call by name with CALL procedure_name(), passing parameters and receiving results back. Stored procedures are the database equivalent of PHP functions: they encapsulate reusable logic, can contain loops, conditionals, and multiple SQL statements, and execute server-side with no round-trips between PHP and MySQL for each statement. For WordPress developers, stored procedures are most valuable for complex batch operations (cleanup, data migrations, report generation) that would otherwise require many sequential $wpdb calls from PHP. The trade-offs are real: procedures live in the database, not in version-controlled PHP files (though you can version their creation SQL), and they are harder to debug than PHP code. They also make your database less portable — if you move from MySQL to PostgreSQL, procedures need to be rewritten. For WordPress specifically, $wpdb can call procedures with $wpdb->query( ‘CALL procedure_name()’ ), though output parameters and result sets require specific handling. This guide complements the transactions guide, the subqueries guide, and the EXPLAIN guide for a complete MySQL advanced feature set.

Problem: You have a complex multi-step database operation (cleanup, report generation, batch update) that requires many sequential SQL statements and you want to encapsulate it as a reusable, callable database routine.

Solution: Create the following stored procedures in phpMyAdmin (SQL tab):

-- Change delimiter so MySQL doesn't interpret ; inside the procedure as the end
DELIMITER $$

-- Procedure 1: clean up orphaned postmeta and term_relationships
CREATE PROCEDURE CleanOrphanedData()
BEGIN
    -- Delete postmeta for posts that no longer exist
    DELETE pm FROM ynYYAa7bFT_postmeta pm
    WHERE NOT EXISTS (
        SELECT 1 FROM ynYYAa7bFT_posts p WHERE p.ID = pm.post_id
    );

    -- Delete term_relationships for posts that no longer exist
    DELETE tr FROM ynYYAa7bFT_term_relationships tr
    WHERE NOT EXISTS (
        SELECT 1 FROM ynYYAa7bFT_posts p WHERE p.ID = tr.object_id
    );

    -- Return how many rows remain in each table
    SELECT 'postmeta' AS tbl, COUNT(*) AS remaining FROM ynYYAa7bFT_postmeta
    UNION ALL
    SELECT 'term_relationships', COUNT(*) FROM ynYYAa7bFT_term_relationships;
END$$

-- Procedure 2: get post count per category with input parameter
CREATE PROCEDURE GetCategoryStats(IN min_posts INT)
BEGIN
    SELECT
        t.name        AS category,
        tt.count      AS post_count,
        t.slug
    FROM ynYYAa7bFT_terms t
    JOIN ynYYAa7bFT_term_taxonomy tt
        ON tt.term_id = t.term_id AND tt.taxonomy = 'category'
    WHERE tt.count >= min_posts
    ORDER BY tt.count DESC;
END$$

-- Procedure 3: batch update post excerpt from content (with OUT parameter)
CREATE PROCEDURE BackfillExcerpts(IN max_posts INT, OUT updated_count INT)
BEGIN
    SET updated_count = 0;

    UPDATE ynYYAa7bFT_posts
    SET post_excerpt = LEFT( REGEXP_REPLACE( post_content, '<[^>]+>', '' ), 160 )
    WHERE post_status  = 'publish'
      AND post_type    = 'post'
      AND post_excerpt = ''
    LIMIT max_posts;

    SET updated_count = ROW_COUNT();
END$$

DELIMITER ;

-- Call the procedures
CALL CleanOrphanedData();
CALL GetCategoryStats(5);

-- Call with OUT parameter
CALL BackfillExcerpts(100, @cnt);
SELECT @cnt AS updated_rows;

-- Drop a procedure
-- DROP PROCEDURE IF EXISTS CleanOrphanedData;

// Call a stored procedure from WordPress via $wpdb
function helloadmin_call_cleanup(): void {
    global $wpdb;
    $wpdb->query( 'CALL CleanOrphanedData()' );
}

// Call procedure with parameter and fetch results
function helloadmin_get_category_stats( int $min_posts = 5 ): array {
    global $wpdb;
    return $wpdb->get_results(
        $wpdb->prepare( 'CALL GetCategoryStats(%d)', $min_posts ),
        ARRAY_A
    );
}

NOTE: Stored procedures are not included in a standard WordPress database export via wp db export (WP-CLI) or phpMyAdmin’s default export — you must explicitly check “Include routines” in phpMyAdmin or use mysqldump --routines from the command line to include them in backups. Always version your procedure creation SQL in a file alongside your plugin code, and use DROP PROCEDURE IF EXISTS followed by CREATE PROCEDURE when deploying updates so you can re-run the script safely.