Stored Procedures Explained

A stored procedure is a named block of SQL (and procedural logic) saved inside the database that you can run on demand. Instead of sending the same multi-step sequence from your application every time, you store it once and call it by name. Procedures bundle related operations, run them close to the data, and can wrap several statements in a single transaction.

What a procedure looks like

Procedures accept parameters, contain multiple statements, and are invoked with CALL. Here is one that enrols a student, written for both major databases (the procedural syntax differs by vendor).

PostgreSQL (using PL/pgSQL):

-- PostgreSQL: CREATE PROCEDURE with PL/pgSQL
CREATE OR REPLACE PROCEDURE enrol_student(
    p_student_id INTEGER,
    p_course     VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Insert the enrolment row
    INSERT INTO enrolment (student_id, course, enrolled_on)
    VALUES (p_student_id, p_course, CURRENT_DATE);

    -- Update a counter on the student record in the same call
    UPDATE student
    SET course_count = course_count + 1
    WHERE student_id = p_student_id;
END;
$$;

-- Call it
CALL enrol_student(1, 'DBMS');

MySQL (using its procedural dialect):

-- MySQL: note DELIMITER change so the ; inside the body is not misread
DELIMITER //
CREATE PROCEDURE enrol_student(
    IN p_student_id INT,
    IN p_course     VARCHAR(150)
)
BEGIN
    INSERT INTO enrolment (student_id, course, enrolled_on)
    VALUES (p_student_id, p_course, CURDATE());

    UPDATE student
    SET course_count = course_count + 1
    WHERE student_id = p_student_id;
END //
DELIMITER ;

-- Call it
CALL enrol_student(1, 'DBMS');

Both do the same thing: two related writes, executed together, behind one name. The procedural language is vendor-specific — PL/pgSQL for PostgreSQL, MySQL's own syntax for MySQL, T-SQL for SQL Server, PL/SQL for Oracle. The concept is standard; the syntax is not.

Parameters

Procedures take parameters in three modes:

• IN — input only (the default in most databases). Used above.
• OUT — the procedure writes a result back into it.
• INOUT — both supplies and returns a value.

-- PostgreSQL: an OUT-style result via INOUT
CREATE OR REPLACE PROCEDURE count_courses(
    p_student_id INTEGER,
    INOUT p_total INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*) INTO p_total
    FROM enrolment
    WHERE student_id = p_student_id;
END;
$$;

Procedure vs function

These are related but distinct:

• A stored procedure performs actions (inserts, updates, multiple steps). It is invoked with CALL and does not have to return a value. It can change data freely.
• A function computes and returns a value and is designed to be used inside a query (e.g. SELECT my_function(age)). Functions are typically restricted from side effects so they are safe to use in expressions.

Rule of thumb: if you want to do something, write a procedure; if you want to compute something to use in a query, write a function.

When procedures help

• Bundling multi-step logic. Several writes that must happen together live in one place and run in one round trip.
• Reducing network chatter. One CALL replaces many statements sent back and forth — useful for batch operations.
• Centralising rules. If multiple applications hit the same database, a procedure ensures they all follow the identical logic.
• Controlling access. You can grant EXECUTE on a procedure without granting direct write access to the underlying tables.

When application code is better

Procedures are not always the right choice:

• Business logic that changes often. Procedural SQL is harder to version-control, test, and review than application code for most teams.
• Portability matters. Procedure syntax is vendor-locked; moving from MySQL to PostgreSQL means rewriting every procedure.
• Complex logic. General-purpose languages have richer tooling, libraries, and debugging than procedural SQL.

A common modern pattern: keep simple data-bundling in procedures (or skip them entirely) and keep substantial business logic in application code. Use procedures where the win — fewer round trips, tighter transactions, centralised access control — is real.

Common mistakes

• Assuming syntax is portable. It is not. PL/pgSQL, MySQL's dialect, T-SQL, and PL/SQL all differ. Plan for a rewrite if you switch databases.
• Forgetting the DELIMITER change in MySQL. Without it, the client treats the first ; inside the body as the end of the statement, and creation fails.
• Putting all business logic in the database. Heavy logic in procedures is hard to test and review. Be selective.
• Not handling errors. A procedure that does two writes should run inside a transaction and roll back on failure — otherwise you get half-completed operations. See transactions & ACID.
• Confusing procedures and functions. Procedures act and are CALLed; functions return and are used in queries. Using the wrong one leads to syntax errors.

FAQ

Are stored procedures faster than queries from my app? They reduce network round trips and run close to the data, which helps for multi-statement work. For a single simple query, the difference is negligible.

Can a procedure return a result set? Yes — most databases let a procedure produce one or more result sets, though the exact mechanism is vendor-specific.

Should I use procedures or keep logic in code? It depends on your team and constraints. Use procedures for tight, data-local operations and access control; keep evolving business logic in code where it is easier to test and review.

Keep learning

Procedures pair naturally with transactions & ACID and are close cousins of triggers in SQL, which run procedural logic automatically on data events.

Want to write procedures that bundle real operations safely? Join the waitlist for the DBMS & SQL course at Infoplanet, Jalgaon.