Stored Procedures and Functions

Stored procedures and functions are named blocks of SQL (and optional procedural logic) that are stored inside the database and can be called by name. They reduce network round trips, centralise business logic, and can improve security by granting execute privileges without exposing underlying tables.

Creating a Stored Procedure

DELIMITER $$

CREATE PROCEDURE GetUserPosts(IN p_user_id INT)
BEGIN
  SELECT id, title, created_at
  FROM posts
  WHERE user_id = p_user_id
  ORDER BY created_at DESC;
END$$

DELIMITER ;

DELIMITER changes the statement delimiter so that the semicolons inside the procedure body do not prematurely end the CREATE PROCEDURE statement. After creation, restore the delimiter to ;.

Call the procedure with:

CALL GetUserPosts(42);

IN, OUT, and INOUT Parameters

Procedures support three parameter modes:

• IN — value passed into the procedure (default)
• OUT — value returned from the procedure
• INOUT — value is both passed in and returned

DELIMITER $$

CREATE PROCEDURE CountUserPosts(IN p_user_id INT, OUT p_count INT)
BEGIN
  SELECT COUNT(*) INTO p_count
  FROM posts
  WHERE user_id = p_user_id;
END$$

DELIMITER ;

CALL CountUserPosts(42, @count);
SELECT @count;

Stored Functions

A stored function returns a single scalar value and can be used inside SQL expressions, unlike procedures which are called with CALL:

DELIMITER $$

CREATE FUNCTION FullName(first_name VARCHAR(50), last_name VARCHAR(50))
RETURNS VARCHAR(101)
DETERMINISTIC
BEGIN
  RETURN CONCAT(first_name, ' ', last_name);
END$$

DELIMITER ;

SELECT FullName(first_name, last_name) FROM employees;

Procedural Logic

Procedures and functions support IF/ELSEIF/ELSE, CASE, WHILE, REPEAT, LOOP, local variables (DECLARE), and cursors for iterating over result sets.

IF p_amount <= 0 THEN
  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Amount must be positive';
END IF;

Managing Procedures

SHOW PROCEDURE STATUS WHERE Db = 'myapp';
DROP PROCEDURE IF EXISTS GetUserPosts;

Stored procedures and functions are a powerful tool for encapsulating complex logic inside the database, reducing duplication, and enforcing consistent business rules across all clients.