Stored Procedures

Explanation

A stored procedure is a named block of SQL (often with control flow like IF, loops, and variables) stored in the database and executed on demand with CALL. Procedures are the workhorses for multi-step operations — nightly reporting, data cleanup, or anything that should run as one transaction on the server.

Procedures differ from functions: a function is typically used inside a query and returns a value, while a procedure does work and is invoked with CALL.

Syntax

-- PostgreSQL / PL/pgSQL style
CREATE PROCEDURE give_raise(dept_id INT, pct NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE employees
  SET salary = salary * (1 + pct)
  WHERE department_id = dept_id;
  COMMIT;
END;
$$;
 
CALL give_raise(1, 0.05);

Interactive Example

Procedural languages aren't available in this in-browser engine, but you can see the effect a procedure would produce: a parameterized raise applied via a single UPDATE statement.

Common Mistakes

• Assuming portability. Procedure syntax is database-specific; a PostgreSQL procedure won't run on SQL Server or MySQL without rewriting.
• Hiding too much logic. Overusing procedures can make logic hard to test and debug from application code.
• Forgetting transactions. Wrap the procedure's statements in a transaction so a failure mid-way doesn't leave partial changes.

Best Practices

• Use procedures for server-side, multi-step, transactional work.
• Keep procedures small and single-purpose.
• Pass values as parameters instead of concatenating them into SQL strings (avoids injection).

Practice Question

Sketch a stored procedure promote_manager(emp_id INT) that sets the given employee's manager_id to NULL (making them a top-level manager) and logs the change — describe the statements it would contain.

Summary

Stored procedures are server-side named programs you run with CALL. They package multi-step, transactional logic, but their syntax is database-specific and not portable across engines.

FAQ