SQL Stored Procedures

Every statement so far has been written out fresh each time it runs. A stored procedure is a named routine, one or more SQL statements, optionally with parameters, saved inside the database itself and called by name, instead of resending the whole statement every time.

Learning Objectives

• Explain what a stored procedure is and why it's reusable in a way a plain query isn't.
• Write a simple stored procedure with a parameter on at least one database.
• Know which database in this course has no stored procedure support at all.

MySQL / MariaDB

DELIMITER // temporarily changes the statement terminator from ; to //, needed because the procedure's own body contains semicolons that shouldn't end the CREATE PROCEDURE statement itself. CALL give_raise(1, 5.0) runs it, giving department 1 (Engineering) a 5% raise.

DELIMITER //

CREATE PROCEDURE give_raise(IN dept_id INT, IN raise_pct DECIMAL(5,2))
BEGIN
  UPDATE employees
  SET salary = salary * (1 + raise_pct / 100)
  WHERE department_id = dept_id;
END //

DELIMITER ;

CALL give_raise(1, 5.0);

SQL Server

Parameters are prefixed with @ throughout, and no DELIMITER trick is needed, SQL Server's batch separator (GO) works differently. EXEC (or EXECUTE) runs the procedure.

CREATE PROCEDURE give_raise
  @dept_id INT,
  @raise_pct DECIMAL(5,2)
AS
BEGIN
  UPDATE employees
  SET salary = salary * (1 + @raise_pct / 100)
  WHERE department_id = @dept_id;
END;

EXEC give_raise @dept_id = 1, @raise_pct = 5.0;

PostgreSQL and Oracle

PostgreSQL (version 11+) needs LANGUAGE plpgsql and the $$ ... $$ body delimiters. Oracle's PL/SQL wraps the body in IS ... BEGIN ... END, and CREATE OR REPLACE lets the same statement update an existing procedure without a separate DROP first.

CREATE PROCEDURE give_raise(dept_id INT, raise_pct NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE employees
  SET salary = salary * (1 + raise_pct / 100)
  WHERE department_id = dept_id;
END;
$$;

CALL give_raise(1, 5.0);

CREATE OR REPLACE PROCEDURE give_raise(
  dept_id IN NUMBER,
  raise_pct IN NUMBER
) IS
BEGIN
  UPDATE employees
  SET salary = salary * (1 + raise_pct / 100)
  WHERE department_id = dept_id;
END;
/

EXEC give_raise(1, 5.0);

SQLite Has No Stored Procedures

Try It Yourself

On MySQL, write a stored procedure that deletes any employee by id, then call it for employee id 99.

DELIMITER //

CREATE PROCEDURE remove_employee(IN emp_id INT)
BEGIN
  DELETE FROM employees WHERE id = emp_id;
END //

DELIMITER ;

CALL remove_employee(99);

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Use clear, descriptive procedure names, the same naming-convention principle from Module 1 applies here too.
• Document each parameter's expected meaning, since the signature alone often isn't self-explanatory.
• Don't assume SQLite support, plan application-level logic instead when SQLite is a target.

Interview Questions

Summary

A stored procedure is a named, reusable routine saved in the database and called by name, with syntax that differs substantially across MySQL/MariaDB, SQL Server, PostgreSQL, and Oracle, and no support at all on SQLite.

What's Next?

SQL Comments next, a short lesson on documenting a query, single-line and block comments, portable everywhere.