Stored Procedures: Logic That Lives in the Database

Quick Answer

A stored procedure is compiled SQL stored in the database and invoked with CALL. It can take IN and OUT parameters. Use it for data-heavy operations near the data; keep application logic in the application.

Creating and calling one

All examples here were run on MySQL 8.

CREATE PROCEDURE top_students(IN min_marks INT)
BEGIN
  SELECT name, marks FROM student
  WHERE marks >= min_marks
  ORDER BY marks DESC;
END

CALL top_students(80);
-- Meera  96
-- Asha   91

The procedure is stored in the database, not in your application. Any client that can connect can call it by name, which is both the main benefit and the main complaint.

A note for anyone typing this into a client: ; inside the body terminates the statement early, so most clients need DELIMITER $$ before and after. That is a client quirk rather than part of the language, and it confuses people the first time.

IN, OUT and INOUT parameters

IN passes a value in — the default. OUT returns one:

CREATE PROCEDURE class_avg(OUT avg_marks DECIMAL(5,2))
BEGIN
  SELECT AVG(marks) INTO avg_marks FROM student;
END

CALL class_avg(@a);
SELECT @a;   -- 85.00

The SELECT ... INTO form assigns a query result to a variable rather than returning it as rows. @a is a session variable holding the output after the call.

INOUT does both — the caller supplies a value and the procedure modifies it.

Procedures can also contain control flow: IF, WHILE, DECLARE for local variables, and cursors for row-by-row processing. That is effectively a small programming language inside the database, and using it heavily is where opinions divide.

Functions versus procedures

A stored function returns a single value and can be used inside a query:

CREATE FUNCTION grade(m INT) RETURNS CHAR(1) DETERMINISTIC
RETURN CASE WHEN m >= 90 THEN 'A'
            WHEN m >= 75 THEN 'B'
            ELSE 'C' END;

SELECT name, grade(marks) AS g FROM student;
-- Asha   A
-- Ravi   C
-- Meera  A

That is the practical distinction: a procedure is called with CALL and can return result sets; a function returns one value and can appear in a SELECT.

DETERMINISTIC declares that the same input always gives the same output, which lets the optimiser cache and replicate safely. Declaring it wrongly on something non-deterministic causes subtle replication bugs.

Be careful calling a function on every row of a large table — it runs per row, and a function containing its own query is a quiet way to turn one query into a million.

The case for them

• Less data over the network. A procedure processing a million rows and returning a summary moves far less data than fetching a million rows to the application. This is the strongest argument.
• One definition for many clients. If a reporting tool, a legacy application and your API all need the same complex query, the database is a genuinely sensible shared place.
• Permissions. Grant execute on a procedure without granting direct table access, so callers can only do the specific thing it does.
• Atomicity. Multi-step operations run inside one transaction close to the data.

The case against, which is why they fell out of favour

Version control is awkward. Procedures live in the database, not naturally in your repository. Teams end up with migration files that may or may not match what is deployed, and "which version is actually running" becomes a real question.

Testing is harder. You need a live database. There is no straightforward unit test for a stored procedure.

Debugging is poor. No breakpoints, limited logging, and errors that surface as opaque database messages.

Logic gets split. Half your business rules in the application and half in the database means every developer must know to look in both places. This is the most common real cost.

Portability. Procedure syntax differs substantially between MySQL, PostgreSQL and SQL Server, so migrating means rewriting them all.

The balanced position, and a good interview answer: use stored procedures for data-intensive operations where moving the data would dominate the cost, and keep business logic in the application where it is versioned, tested and reviewable. The same reasoning applies to triggers.

Frequently Asked Questions