SQL Stored Procedures – Reusable Server-Side Logic

What Are Stored Procedures?

A stored procedure is a precompiled, named unit of SQL code saved in the database catalog. Unlike a plain SQL query executed from application code, a stored procedure lives inside the database server and is called by name. The database parses and compiles it once; subsequent calls skip the parse step, which can improve performance for frequently executed logic.

ℹ️

Why use stored procedures?

Stored procedures are useful when: (1) you need to perform multiple related SQL operations atomically, (2) you want to enforce business rules in the database layer regardless of which application calls it, (3) you want to reduce network round-trips by doing all the work server-side, or (4) you need conditional logic (IF/ELSE, loops) around SQL statements.

Creating a Stored Procedure (MySQL)

In MySQL, you must change the delimiter before defining a procedure so that semicolons inside the body are not treated as end-of-statement by the client.

SQL – MySQL

-- Change the delimiter so ; inside the body doesn't end the CREATE
DELIMITER $$

CREATE PROCEDURE give_raise(
  IN  emp_id   INT,
  IN  pct      DECIMAL(5,2),
  OUT new_sal  DECIMAL(10,2)
)
BEGIN
  -- Validate percentage
  IF pct <= 0 OR pct > 100 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Raise percentage must be between 0 and 100';
  END IF;

  -- Apply the raise
  UPDATE employees
  SET salary = salary * (1 + pct / 100)
  WHERE id = emp_id;

  -- Return the new salary via OUT parameter
  SELECT salary INTO new_sal
  FROM employees
  WHERE id = emp_id;
END$$

DELIMITER ;

A simpler example — logging an event

SQL – MySQL

DELIMITER $$

CREATE PROCEDURE log_action(
  IN  user_id   INT,
  IN  action    VARCHAR(100),
  IN  details   TEXT
)
BEGIN
  INSERT INTO audit_log (user_id, action, details, created_at)
  VALUES (user_id, action, details, NOW());
END$$

DELIMITER ;

Parameters: IN, OUT, INOUT

MySQL (and most databases) support three parameter modes:

Mode	Description	Example Use
IN	Value passed in from caller; procedure cannot modify the caller's variable	Filter criteria, IDs, amounts
OUT	Value returned to caller; starts as NULL inside the procedure	Returning a calculated result or new ID
INOUT	Caller passes a value in; procedure can modify it and return new value	Running totals, counters

Calling a Stored Procedure

SQL – MySQL

-- Call a procedure with no OUT parameter
CALL log_action(7, 'login', 'User logged in from 192.168.1.1');

-- Call a procedure with an OUT parameter
SET @new_salary = 0;
CALL give_raise(42, 10.00, @new_salary);
SELECT @new_salary AS updated_salary;

-- Call inside a transaction
START TRANSACTION;
CALL give_raise(42, 10.00, @new_salary);
CALL give_raise(55, 10.00, @new_salary);
COMMIT;

Dropping a Stored Procedure

SQL

-- Drop a procedure
DROP PROCEDURE give_raise;

-- Drop only if exists (no error if not found)
DROP PROCEDURE IF EXISTS give_raise;

-- List all stored procedures in MySQL
SHOW PROCEDURE STATUS WHERE Db = 'my_database';

-- View procedure source in MySQL
SHOW CREATE PROCEDURE give_raise;

Stored Procedures vs Functions

Procedures and functions look similar but serve different roles:

Feature	Stored Procedure	Function
Returns a value	Via OUT parameter (optional)	Always returns exactly one value
Can be used in SELECT	No — called with CALL	Yes — `SELECT my_func(arg)`
Side effects (INSERT/UPDATE)	Yes — fully supported	Restricted — pure functions should have none
Transactions	Can contain transaction control (COMMIT/ROLLBACK)	Cannot (in most databases)
Called with	`CALL proc_name(...)`	`SELECT func_name(...)`

PostgreSQL stored procedure (PL/pgSQL)

SQL – PostgreSQL

-- PostgreSQL uses PL/pgSQL and dollar-quoting
CREATE OR REPLACE PROCEDURE transfer_funds(
  sender_id   INT,
  receiver_id INT,
  amount      NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
  -- Deduct from sender
  UPDATE accounts SET balance = balance - amount WHERE id = sender_id;

  -- Add to receiver
  UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;

  -- Log the transfer
  INSERT INTO transactions (sender_id, receiver_id, amount, transferred_at)
  VALUES (sender_id, receiver_id, amount, NOW());

  COMMIT;
EXCEPTION WHEN OTHERS THEN
  ROLLBACK;
  RAISE;
END;
$$;

-- Call in PostgreSQL
CALL transfer_funds(1, 2, 500.00);

When to Use Stored Procedures

Multi-step business logic — operations that require multiple INSERT/UPDATE/DELETE statements that must succeed or fail together.
Batch operations — nightly jobs, bulk updates, data migrations that run inside the database without round-trips.
Enforcing business rules at the DB layer — when you need guarantees that hold regardless of which application or user makes changes.
Reducing network traffic — a single CALL proc(...) replaces dozens of individual queries sent over the network.

⚠️

Stored procedures have trade-offs

Logic in stored procedures is harder to version control, test, and deploy than application code. Over-reliance on procedures can create a "fat database" where business logic is split between the application and the DB, making both harder to maintain. Use them judiciously — not as a default for all logic.

Summary

📋 Summary

A stored procedure is a named, saved block of SQL stored in the database and called with CALL.
Parameters can be IN (input), OUT (output), or INOUT (both).
In MySQL use DELIMITER $$ to prevent semicolons inside the body from terminating the CREATE statement prematurely.
Procedures can contain DML (INSERT/UPDATE/DELETE), conditional logic, and transaction control.
Functions return a single value and can be used in SELECT; procedures are called with CALL and communicate results via OUT parameters.
PostgreSQL uses PL/pgSQL with dollar-quoting ($$...$$); SQL Server uses T-SQL.
Best for: multi-step atomic operations, batch jobs, enforcing DB-level business rules.

FAQ

Are stored procedures precompiled? +

In MySQL, stored procedures are parsed and stored in compiled form. In PostgreSQL, PL/pgSQL procedures are compiled on first execution and the compiled plan is cached for the session. The performance benefit of "precompilation" is real but often overstated — the bigger benefit of stored procedures is usually reduced network round-trips and the ability to use procedural logic alongside SQL.

Can stored procedures return a result set? +

In MySQL, yes — a SELECT statement inside a procedure without INTO sends a result set back to the caller, just like a plain query. In PostgreSQL, procedures don't return result sets directly; you would use a function with RETURNS TABLE or RETURNS SETOF for that purpose. In SQL Server, SELECT inside a procedure also returns a result set to the caller.

How do I handle errors inside a stored procedure? +

In MySQL, use DECLARE ... HANDLER FOR SQLEXCEPTION or SIGNAL SQLSTATE to raise errors. In PostgreSQL PL/pgSQL, use EXCEPTION WHEN ... THEN blocks (similar to try/catch). In SQL Server T-SQL, use TRY...CATCH blocks. Always pair error handling with transaction rollback to leave the database in a consistent state on failure.

What is the difference between stored procedures in MySQL vs PostgreSQL? +

MySQL procedures use DELIMITER $$ and a relatively simple procedural language. PostgreSQL uses PL/pgSQL with dollar-quoting, richer exception handling, and stricter typing. In PostgreSQL (before version 11), "stored procedures" were implemented as functions — true CREATE PROCEDURE with transaction control was added in PostgreSQL 11. PostgreSQL also supports other procedural languages: PL/Python, PL/Perl, and PL/v8 (JavaScript).