SQL · Quick Reference

Lesson 15 — Stored Procedures, Functions & Transactions Cheat Sheet

SQL

In one line: A stored procedure is a named block of SQL saved on the database server. Call it with CALL. It can accept IN parameters (input) and OUT parameters (output).

Key Ideas

1Stored Procedures — Reusable SQL Routines. A stored procedure is a named block of SQL saved on the database server. Call it with CALL. It can accept IN parameters (input) and OUT parameters (output).

2OUT Parameters. OUT parameters allow a procedure to return a value back to the caller. Store the result in a session variable prefixed with @.

3Stored Functions. A stored function is like a procedure but it returns a single value and can be called inside a SELECT statement. The DETERMINISTIC keyword tells MySQL the same inputs ...

4ACID Transactions. A transaction groups multiple SQL statements into an atomic unit — either all succeed (COMMIT) or all are undone (ROLLBACK). The ACID properties define what makes a tr...

5Triggers — Auto-Firing SQL. A trigger automatically executes a block of SQL before or after an INSERT, UPDATE, or DELETE on a table — without any explicit CALL.

Code Examples

DELIMITER //
CREATE PROCEDURE GetStudentsByGrade(IN grade_param VARCHAR(5))
BEGIN
  SELECT name, age, email
  FROM students
  WHERE grade = grade_param
  ORDER BY name;
END //
DELIMITER ;

-- Call it:
CALL GetStudentsByGrade('A');
CALL GetStuden...

DELIMITER //
CREATE PROCEDURE CountByGrade(
  IN  grade_param   VARCHAR(5),
  OUT student_count INT
)
BEGIN
  SELECT COUNT(*) INTO student_count
  FROM students
  WHERE grade = grade_param;
END //
DELIMITER ;

CALL CountByGrade('A', @result);
S...

DELIMITER //
CREATE FUNCTION GetLetterGrade(score INT)
RETURNS VARCHAR(2) DETERMINISTIC
BEGIN
  IF score >= 90 THEN RETURN 'A+';
  ELSEIF score >= 80 THEN RETURN 'A';
  ELSEIF score >= 70 THEN RETURN 'B';
  ELSEIF score >= 60 THEN RETURN ...