SQL — Stored Procedures

Basic procedure

-- MySQL
DELIMITER //
CREATE PROCEDURE get_users()
BEGIN
  SELECT * FROM users;
END //
DELIMITER ;

CALL get_users();

Parameters

-- MySQL
DELIMITER //
CREATE PROCEDURE get_user_by_id(IN user_id INT)
BEGIN
  SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

CALL get_user_by_id(1);

INOUT parameters

DELIMITER //
CREATE PROCEDURE double_value(INOUT val INT)
BEGIN
  SET val = val * 2;
END //
DELIMITER ;

SET @num = 5;
CALL double_value(@num);
SELECT @num; -- 10

Conditional logic

DELIMITER //
CREATE PROCEDURE get_user_status(IN user_id INT)
BEGIN
  DECLARE user_status VARCHAR(20);

  SELECT status INTO user_status
  FROM users WHERE id = user_id;

  IF user_status = 'active' THEN
    SELECT 'User is active' AS message;
  ELSE
    SELECT 'User is inactive' AS message;
  END IF;
END //
DELIMITER ;

Loops

DELIMITER //
CREATE PROCEDURE count_to(IN target INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= target DO
    SELECT i AS current_number;
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

CALL count_to(5);

Error handling

DELIMITER //
CREATE PROCEDURE safe_divide(IN a DECIMAL, IN b DECIMAL)
BEGIN
  DECLARE EXIT HANDLER FOR 1365
  BEGIN
    SELECT 'Division by zero' AS error;
  END;

  SELECT a / b AS result;
END //
DELIMITER ;

Mini Practice

Write SQL code that:

1. Creates a stored procedure with parameters
2. Uses INOUT parameters
3. Implements conditional logic
4. Adds error handling

Up Next

In the next lesson, you'll learn about Functions — creating custom SQL functions.