Chapter 19: Stored Procedures and Functions

Procedural SQL lets you package logic close to the data. You can create PROCEDURE blocks that perform actions and FUNCTION blocks that return values. The ANSI core describes the idea of routines, while each engine supplies its own procedural dialect. Use routines to reduce round trips, to encapsulate business rules, and to share logic across applications.

When to use procedural SQL

Procedural code inside the database can be helpful, but it is not always the right tool. Start with set-based SQL, then reach for routines when you need orchestration, reusable logic, or server-side validation.

Good use cases

Use a PROCEDURE when you need multi-step workflows (validate input, insert rows, update aggregates). Use a FUNCTION when an expression needs a computed value (normalize a code, compute a score). Routines are helpful for enforcing rules that must run no matter which client calls the database.

When to avoid

Avoid routines when a single declarative statement does the job. If a query can be expressed as INSERT … SELECT or a well-written UPDATE with a JOIN, prefer the set-based version. Keep heavy business logic in application code when portability, versioning, and testing outweigh server-side benefits.

Portability considerations

Each vendor uses different routine syntax. MySQL and MariaDB use CREATE PROCEDURE and CREATE FUNCTION with BEGIN and END. PostgreSQL uses CREATE FUNCTION and CREATE PROCEDURE with LANGUAGE plpgsql or another language. SQL Server uses CREATE PROCEDURE and CREATE FUNCTION with T-SQL syntax (use of inserted and deleted pseudo-tables for triggers). Keep bodies simple, and isolate vendor specifics in the smallest possible surface area.

Variables, parameters, and return values

Routines introduce parameters, local variables, control flow, and return mechanisms. The details vary per engine; the goals are the same: validate input, compute results, and return a clear outcome.

CREATE PROCEDURE essentials

A PROCEDURE encapsulates actions. It can have IN, OUT, and INOUT parameters (support depends on the engine). It does not return a value in expressions. You call it with CALL (MySQL) or EXEC/EXECUTE (SQL Server, some others).

-- MySQL / MariaDB example
DELIMITER //
CREATE PROCEDURE add_user(IN p_email VARCHAR(255), IN p_name VARCHAR(100), OUT p_user_id BIGINT)
BEGIN
  DECLARE v_exists INT DEFAULT 0;

  SELECT COUNT(*) INTO v_exists
  FROM users
  WHERE email = p_email;

  IF v_exists > 0 THEN
    SET p_user_id = NULL;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email already exists';
  ELSE
    INSERT INTO users(email, full_name, created_at)
    VALUES(p_email, p_name, NOW());
    SET p_user_id = LAST_INSERT_ID();
  END IF;
END//
DELIMITER ;

CALL add_user('a@example.com', 'A User', @new_id);
SELECT @new_id;

-- SQL Server example
CREATE OR ALTER PROCEDURE dbo.AddUser
  @Email NVARCHAR(255),
  @Name  NVARCHAR(100),
  @UserId BIGINT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  IF EXISTS(SELECT 1 FROM dbo.Users WHERE Email = @Email)
  BEGIN
    SET @UserId = NULL;
    THROW 50000, 'Email already exists', 1;
  END;

  INSERT INTO dbo.Users(Email, FullName, CreatedAt)
  VALUES(@Email, @Name, SYSDATETIME());

  SET @UserId = SCOPE_IDENTITY();
END;

DECLARE @id BIGINT;
EXEC dbo.AddUser @Email = N'a@example.com', @Name = N'A User', @UserId = @id OUTPUT;
SELECT @id AS NewId;

CREATE FUNCTION essentials

A FUNCTION returns a value and can be used inside expressions. It typically has only IN parameters. Keep functions deterministic when possible, and avoid side effects (engines may restrict data modification inside a function).

-- PostgreSQL example (plpgsql)
CREATE OR REPLACE FUNCTION public.slugify(p_text text)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
  v_text text := lower(trim(p_text));
BEGIN
  -- replace spaces and non-word characters
  v_text := regexp_replace(v_text, '\s+', '-', 'g');
  v_text := regexp_replace(v_text, '[^a-z0-9\-]', '', 'g');
  RETURN v_text;
END;
$$;

SELECT slugify('Hello World & More');  -- returns 'hello-world-more'

Local variables and control flow

Engines provide DECLARE for locals, and IF, CASE, LOOP or WHILE for control flow. Use them sparingly; first consider whether a set-based expression is clearer.

-- MySQL / MariaDB: loop and exit
CREATE PROCEDURE demo_loop(IN p_n INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  my_loop: LOOP
    IF i > p_n THEN
      LEAVE my_loop;
    END IF;
    INSERT INTO t(counter) VALUES(i);
    SET i = i + 1;
  END LOOP my_loop;
END;

Error handling and signaling

Use the engine’s error constructs to signal problems. MySQL uses SIGNAL. PostgreSQL uses RAISE. SQL Server uses THROW. Prefer meaningful messages and consistent SQLSTATE or error numbers.

-- PostgreSQL
CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
  IF b = 0 THEN
    RAISE EXCEPTION 'Division by zero';
  END IF;
  RETURN a / b;
END;
$$;

Transactions inside routines

Transaction control varies. PostgreSQL functions run inside the caller’s transaction (you cannot COMMIT in a function). SQL Server procedures may issue BEGIN TRAN and COMMIT. MySQL procedures run within the current transaction; you can use START TRANSACTION and COMMIT if the storage engine supports it. Keep boundaries clear and avoid long transactions inside routines.

Triggers

A trigger runs automatically in response to table events. Use triggers to enforce invariants, maintain audit trails, and derive values. Keep trigger bodies brief and predictable.

Trigger timing and events

Common timings are BEFORE, AFTER, and sometimes INSTEAD OF (often for views). Common events are INSERT, UPDATE, and DELETE. You can create separate triggers per event or a combined trigger depending on the engine.

Accessing rows inside triggers

MySQL and PostgreSQL use NEW and OLD row references. SQL Server uses the virtual tables inserted and deleted. Use these to read changed values and to validate or transform data.

-- MySQL: BEFORE INSERT to normalize and enforce
CREATE TRIGGER users_bi BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.email = LOWER(TRIM(NEW.email));
  IF NEW.email IS NULL OR NEW.email = '' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email required';
  END IF;
END;

-- PostgreSQL: AFTER UPDATE audit trail
CREATE TABLE user_audit(
  user_id bigint,
  changed_at timestamptz,
  old_email text,
  new_email text
);

CREATE OR REPLACE FUNCTION audit_user_email()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.email IS DISTINCT FROM OLD.email THEN
    INSERT INTO user_audit(user_id, changed_at, old_email, new_email)
    VALUES(NEW.id, now(), OLD.email, NEW.email);
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER users_au
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_user_email();

-- SQL Server: AFTER INSERT trigger to maintain a counter
CREATE OR ALTER TRIGGER dbo.Users_AI ON dbo.Users
AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE dbo.Counters
  SET UserCount = UserCount + (SELECT COUNT(*) FROM inserted)
  WHERE CounterName = 'Users';
END;

Order, recursion, and performance

Trigger firing order can be unspecified or configurable. Some engines allow multiple triggers per timing; if order matters, consolidate logic into one trigger. Watch for unintended recursion when a trigger modifies the same table; guard with conditions or use session settings to disable nesting if available.

Best practices

Keep bodies small. Avoid network calls. Avoid nondeterministic behavior that surprises callers. Document every trigger with purpose and invariants. Test using representative data and concurrent sessions to catch locking issues early.