Stored Procedures & Functions

Every query so far on this site has been sent to MySQL fresh, in full, every single time it runs. A stored procedure takes a block of SQL logic and saves it inside the database itself, under a name - so instead of resending the whole query text every time, an application (or another script) just calls it by name. This lesson covers stored procedures, stored functions, and how to decide when reaching for either one actually makes sense.

DELIMITER - Why It's Needed

Before writing a procedure, there's one genuinely confusing mechanic to get past. Normally, MySQL treats a semicolon (;) as "end of statement, run this now." But the body of a stored procedure is often several statements, each properly ending in its own semicolon - and if MySQL kept its normal behavior, it would think the procedure definition ended at the very first semicolon inside the body, long before you meant it to.

The fix is the DELIMITER command, which temporarily changes what MySQL treats as "end of statement" while you define the procedure. Switching to // (a common convention, though any symbol works) means semicolons inside the body are just ordinary characters again - only // ends a statement, until you switch the delimiter back to ; at the end.

CREATE PROCEDURE - A Worked Example

Here's a procedure that fetches every review for a given movie - logic that would otherwise be a SELECT an application rewrites and resends every time it needs this exact lookup:

DELIMITER //

CREATE PROCEDURE GetMovieReviews(IN movie_id_param INT)
BEGIN
  SELECT * FROM reviews WHERE movie_id = movie_id_param;
END//

DELIMITER ;

With the procedure saved, running it for movie 5 is now just:

CALL GetMovieReviews(5);

CALL executes the procedure and returns its result set exactly as if you'd typed the SELECT yourself - except now that logic lives in one place, under one name, instead of being copy-pasted everywhere it's needed.

IN, OUT, and INOUT Parameters

GetMovieReviews above uses an IN parameter - a value passed into the procedure. MySQL also supports two other parameter directions:

Here's a procedure that uses an OUT parameter to hand back a movie's average rating, rather than returning it as a result set row:

DELIMITER //

CREATE PROCEDURE GetMovieAverageRating(
  IN movie_id_param INT,
  OUT avg_rating_out DECIMAL(3,2)
)
BEGIN
  SELECT AVG(rating) INTO avg_rating_out
  FROM reviews
  WHERE movie_id = movie_id_param;
END//

DELIMITER ;

Calling it means providing a session variable to receive the output, then reading that variable back:

CALL GetMovieAverageRating(5, @avg);
SELECT @avg;

Stored Functions - Similar, but Usable Inside a Query

A stored function looks similar to a procedure at first glance, but it's different in a way that matters: a function always returns exactly one value, and - unlike a procedure - it can be called directly inside another SQL statement, wherever an expression is allowed.

DELIMITER //

CREATE FUNCTION MovieAverageRating(movie_id_param INT)
RETURNS DECIMAL(3,2)
DETERMINISTIC
BEGIN
  DECLARE avg_rating DECIMAL(3,2);
  SELECT AVG(rating) INTO avg_rating
  FROM reviews
  WHERE movie_id = movie_id_param;
  RETURN avg_rating;
END//

DELIMITER ;

Once defined, the function can be dropped straight into a SELECT, once per movie:

SELECT title, MovieAverageRating(id) AS avg_rating
FROM movies;

That's the key difference from GetMovieAverageRating above - the procedure version has to be CALLed on its own and read back through a variable, while the function version reads naturally as part of an ordinary query.

DETERMINISTIC - A Correctness Contract

Notice the DETERMINISTIC keyword in the function definition above. It's a promise you're making to MySQL: given the same input, this function will always return the same result. MovieAverageRating(5) genuinely satisfies that - averaging the same underlying rows always produces the same number. MySQL requires this declaration for certain contexts (like some replication and optimization scenarios), and it's a real contract, not just documentation - a function that reads the current date/time or a random value, for example, would be lying if it claimed DETERMINISTIC.

When to Reach for Stored Procedures and Functions

Moving logic into the database has genuine upsides: it can reduce network round-trips (one CALL instead of several separate queries sent back and forth), and it centralizes logic that multiple applications might otherwise duplicate. But it comes with a real cost too - that logic now lives outside your application's codebase, as covered in Connecting from Application Code, which typically means it isn't reviewed in the same pull requests, isn't covered by the same test suite, and isn't deployed alongside the rest of your application's changes the way ordinary application code is. Neither approach is universally correct - it's a genuine tradeoff between centralizing logic close to the data versus keeping all your business logic in one version-controlled, testable place.