MySQL Stored Procedures and Functions: Complete Guide with Examples

Stored Procedures and Functions

Stored procedures encapsulate SQL logic on the database server, reducing network round-trips and enabling code reuse. Functions are similar but return a value and can be used in SELECT statements.

Creating a Stored Procedure

DELIMITER //

CREATE PROCEDURE GetUserPosts(
    IN  p_user_id  INT,
    IN  p_status   VARCHAR(20),
    OUT p_count    INT
)
BEGIN
    -- Declare variables
    DECLARE v_count INT DEFAULT 0;

    -- Main query
    SELECT COUNT(*) INTO v_count
    FROM posts
    WHERE user_id = p_user_id
      AND status  = p_status;

    SET p_count = v_count;

    -- Return the posts
    SELECT id, title, views, published_at
    FROM   posts
    WHERE  user_id = p_user_id
      AND  status  = p_status
    ORDER BY published_at DESC;
END //

DELIMITER ;

-- Call the procedure
CALL GetUserPosts(1, 'published', @count);
SELECT @count AS total_posts;

IF, CASE, LOOP in Procedures

DELIMITER //

CREATE PROCEDURE AwardBadge(IN p_user_id INT)
BEGIN
    DECLARE v_post_count INT;
    DECLARE v_badge      VARCHAR(50);

    SELECT COUNT(*) INTO v_post_count
    FROM posts WHERE user_id = p_user_id AND status = 'published';

    -- IF-ELSEIF-ELSE
    IF v_post_count >= 100 THEN
        SET v_badge = 'prolific-author';
    ELSEIF v_post_count >= 50 THEN
        SET v_badge = 'active-author';
    ELSEIF v_post_count >= 10 THEN
        SET v_badge = 'contributor';
    ELSE
        SET v_badge = 'newbie';
    END IF;

    -- CASE expression
    SET v_badge = CASE
        WHEN v_post_count >= 100 THEN 'prolific-author'
        WHEN v_post_count >= 50  THEN 'active-author'
        ELSE 'contributor'
    END;

    INSERT IGNORE INTO user_badges (user_id, badge_slug, awarded_at)
    VALUES (p_user_id, v_badge, NOW());
END //

DELIMITER ;

Stored Functions

DELIMITER //

-- Function: returns a single value, usable in SELECT
CREATE FUNCTION ReadingTime(p_content TEXT)
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE v_words INT;
    SET v_words = LENGTH(p_content) - LENGTH(REPLACE(p_content, ' ', '')) + 1;
    RETURN CEILING(v_words / 200); -- 200 words per minute
END //

DELIMITER ;

-- Use function in a query
SELECT title, ReadingTime(content) AS minutes FROM posts;
SELECT * FROM posts WHERE ReadingTime(content) <= 5; -- short posts only

Transactions in Procedures

DELIMITER //

CREATE PROCEDURE TransferPoints(
    IN p_from_user INT,
    IN p_to_user   INT,
    IN p_points    INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Transfer failed';
    END;

    START TRANSACTION;

    UPDATE users SET reputation = reputation - p_points WHERE id = p_from_user;
    UPDATE users SET reputation = reputation + p_points WHERE id = p_to_user;

    COMMIT;
END //

DELIMITER ;

CALL TransferPoints(1, 2, 100);