MySQL Stored Procedures and Stored Functions

Stored programs are SQL code saved in the database and executed on the server.

A first procedure

DELIMITER //

CREATE PROCEDURE get_students_by_dept(IN p_dept VARCHAR(10))
BEGIN
  SELECT id, name_en, cgpa
  FROM students
  WHERE department = p_dept
  ORDER BY cgpa DESC;
END //

DELIMITER ;

CALL get_students_by_dept('CSE');

DELIMITER // temporarily changes the statement terminator so the semicolons inside the body are not treated as the end of the CREATE statement.

Parameter modes

Mode	Direction
`IN` (default)	Caller → procedure
`OUT`	Procedure → caller
`INOUT`	Both

DELIMITER //
CREATE PROCEDURE dept_stats(
  IN  p_dept VARCHAR(10),
  OUT p_count INT,
  OUT p_avg DECIMAL(4,2)
)
BEGIN
  SELECT COUNT(*), ROUND(AVG(cgpa),2)
  INTO p_count, p_avg
  FROM students WHERE department = p_dept;
END //
DELIMITER ;

CALL dept_stats('CSE', @n, @avg);
SELECT @n, @avg;

Variables and control flow

DELIMITER //
CREATE PROCEDURE grade_report(IN p_id INT)
BEGIN
  DECLARE v_cgpa DECIMAL(3,2) DEFAULT 0;
  DECLARE v_grade VARCHAR(20);

  SELECT cgpa INTO v_cgpa FROM students WHERE id = p_id;

  IF v_cgpa >= 3.75 THEN SET v_grade = 'Excellent';
  ELSEIF v_cgpa >= 3.00 THEN SET v_grade = 'Good';
  ELSE SET v_grade = 'Needs work';
  END IF;

  SELECT p_id AS student_id, v_cgpa AS cgpa, v_grade AS grade;
END //
DELIMITER ;

Loop constructs: WHILE … DO … END WHILE, REPEAT … UNTIL … END REPEAT, and LOOP … LEAVE label … END LOOP. CASE … WHEN … END CASE works as a statement too.

Error handling and transactions

DELIMITER //
CREATE PROCEDURE enroll(IN p_student INT, IN p_course INT)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
    INSERT INTO enrollments (student_id, course_id) VALUES (p_student, p_course);
    UPDATE courses SET seats = seats - 1 WHERE id = p_course;
  COMMIT;
END //
DELIMITER ;

Use SIGNAL to raise your own errors:

IF p_seats < 0 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Course is full';
END IF;

Stored functions

A function returns a single value and can be used inside a SELECT.

DELIMITER //
CREATE FUNCTION letter_grade(p_cgpa DECIMAL(3,2))
RETURNS CHAR(2)
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN CASE
    WHEN p_cgpa >= 3.75 THEN 'A+'
    WHEN p_cgpa >= 3.50 THEN 'A'
    WHEN p_cgpa >= 3.00 THEN 'B'
    ELSE 'C' END;
END //
DELIMITER ;

SELECT name_en, letter_grade(cgpa) FROM students;

Functions must declare DETERMINISTIC, NO SQL or READS SQL DATA when binary logging is on.

Procedure vs function

	Procedure	Function
Called with	`CALL`	Inside an expression
Returns	Zero or more result sets, `OUT` params	Exactly one value
Can modify data	Yes	Discouraged/limited
Transactions	Yes	No

Managing stored programs

SHOW PROCEDURE STATUS WHERE db = DATABASE();
SHOW CREATE PROCEDURE get_students_by_dept\G
DROP PROCEDURE IF EXISTS get_students_by_dept;

Weigh the trade-off: stored programs keep logic close to the data and reduce round trips, but they are harder to version-control, test and debug than application code. Many teams use them only for data-intensive batch work.

Stored Procedures & Functions