Stored Procedures and Parameters

Concept

A stored procedure is a named block of SQL and procedural code stored in the database and invoked with CALL. It can take parameters, declare variables, branch, loop, manage transactions and return result sets.

Syntax

DELIMITER $$

CREATE PROCEDURE procedure_name(
    IN    param1 INT,
    OUT   param2 DECIMAL(10,2),
    INOUT param3 INT
)
BEGIN
    -- statements
END$$

DELIMITER ;

CALL procedure_name(1, @result, @counter);
SELECT @result;

DELIMITER $$ is a MySQL client instruction, not SQL. It temporarily changes the statement terminator so the semicolons inside the body do not end the CREATE PROCEDURE statement early. PostgreSQL uses dollar quoting instead; SQL Server needs neither.

Example

DELIMITER $$

CREATE PROCEDURE give_department_raise(
    IN  p_dept_id INT,
    IN  p_percent DECIMAL(5,2),
    OUT p_affected INT
)
BEGIN
    DECLARE v_max_percent DECIMAL(5,2) DEFAULT 20.00;

    IF p_percent <= 0 OR p_percent > v_max_percent THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Raise percentage must be between 0 and 20';
    END IF;

    START TRANSACTION;

    UPDATE employees
    SET    salary = salary * (1 + p_percent / 100)
    WHERE  dept_id = p_dept_id
      AND  status  = 'active';

    SET p_affected = ROW_COUNT();

    COMMIT;
END$$

DELIMITER ;

CALL give_department_raise(10, 7.5, @rows);
SELECT @rows AS employees_updated;

Explanation

Three things make this worth being a procedure rather than a query in application code:

• Validation happens next to the data. The 20 percent cap holds no matter which application calls it.
• The transaction boundary is inside. Caller and network cannot leave it half applied.
• ROW_COUNT() is returned. The caller learns how much changed without a second round trip.

Parameter modes

Control flow

DELIMITER $$
CREATE PROCEDURE archive_old_orders(IN p_before DATE, OUT p_moved INT)
BEGIN
    DECLARE v_batch INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    SET p_moved = 0;
    START TRANSACTION;

    move_loop: LOOP
        DELETE FROM orders WHERE order_date < p_before LIMIT 500;
        SET v_batch  = ROW_COUNT();
        SET p_moved  = p_moved + v_batch;
        IF v_batch = 0 THEN
            LEAVE move_loop;
        END IF;
    END LOOP;

    COMMIT;
END$$
DELIMITER ;

The EXIT HANDLER is the important part: without it, an error mid loop leaves the transaction open and the work half done. Every procedure that writes should have one.

Managing procedures

DROP PROCEDURE IF EXISTS give_department_raise;
SHOW CREATE PROCEDURE give_department_raise;              -- MySQL
SELECT routine_name, routine_type FROM information_schema.routines
WHERE  routine_schema = DATABASE();                        -- standard

Dialect names

When to use one, and when not

Important rules

• Procedures are invoked with CALL (or EXEC); they are not expressions and cannot appear inside a SELECT.
• A procedure may return several result sets, or none.
• Procedure code is not portable between products - it is a rewrite, not a port.
• ROW_COUNT() reflects the immediately preceding statement, so capture it at once.
• Procedures run with definer or invoker privileges; in MySQL the default is DEFINER, which is a security decision worth making consciously.

Common mistakes

• Forgetting DELIMITER and getting a syntax error at the first internal semicolon.
• Omitting an error handler, leaving transactions open after a failure.
• Row by row cursor loops where one set based statement would do the whole job far faster.
• Putting core business rules in procedures that no test suite covers.
• Assuming SQLite supports them.

Best practices

• Keep procedures short and single purpose.
• Validate inputs early and raise a clear error with SIGNAL.
• Always add an exception handler that rolls back and re raises.
• Store the definitions in version controlled migration files.
• Prefer set based statements over loops; a loop in SQL is usually a design smell.

Practice

1. Write a procedure that takes a customer id and returns their order count and lifetime value as OUT parameters.
2. Add validation that raises an error if the customer does not exist.
3. Rewrite a cursor loop that updates rows one at a time as a single UPDATE.