MySQL Stored Procedures: When and How to Use Them

Home » MySQL » MySQL Stored Procedures: When and How to Use Them
October 25, 2018 MySQL
MySQL Stored Procedures: When and How to Use Them

A stored procedure is a named block of SQL saved in the database and executed by name. For WordPress developers, they’re most useful for complex data migrations, batch operations on large tables, or encapsulating multi-step logic that would otherwise require multiple round-trips from PHP.

Problem: When does it make sense to use a MySQL stored procedure instead of running the query from PHP, and how do you create and call one?

Solution: Use stored procedures when the same complex query runs from multiple places, when you want to keep logic in the database layer, or when you need to reduce query round-trips — create them with CREATE PROCEDURE and call them with CALL.

Creating and calling a simple stored procedure:

DELIMITER //

CREATE PROCEDURE get_published_posts_count( IN post_type VARCHAR(20), OUT total INT )
BEGIN
    SELECT COUNT(*) INTO total
    FROM   wp_posts
    WHERE  post_type   = post_type
    AND    post_status = 'publish';
END //

DELIMITER ;

-- Call it
CALL get_published_posts_count( 'post', @count );
SELECT @count;

A procedure for bulk-updating post meta — useful during migrations:

DELIMITER //

CREATE PROCEDURE migrate_meta_key( IN old_key VARCHAR(255), IN new_key VARCHAR(255) )
BEGIN
    DECLARE done INT DEFAULT 0;

    -- Rename the meta key for all posts
    UPDATE wp_postmeta
    SET    meta_key = new_key
    WHERE  meta_key = old_key;

    SELECT ROW_COUNT() AS rows_updated;
END //

DELIMITER ;

CALL migrate_meta_key( '_old_field_name', '_new_field_name' );

Calling a stored procedure from PHP via $wpdb:

global $wpdb;
$wpdb->query(
    $wpdb->prepare( "CALL migrate_meta_key( %s, %s )", '_old_key', '_new_key' )
);

NOTE: Stored procedures are database-specific — they won't transfer automatically when you migrate between MySQL and MariaDB versions, or to a different database engine. Use them for one-off migrations and data operations, not for application logic that belongs in PHP.

Related posts