Writing Stored Procedures and Functions

Some database engines let you encapsulate logic inside the database itself. Stored procedures and functions can insert enrollments, calculate student metrics, or enforce business rules close to the data. SQLite does not support stored procedures natively, but PostgreSQL, MySQL, and SQL Server do. The examples below use PostgreSQL syntax; adapt as needed for your environment.

Learning Goals

See how stored routines can manage University data
Understand the difference between procedures and functions
Handle parameters, return values, and error conditions
Decide when database-side code makes sense

Stored Procedure Example

The registrar wants a single call to register a student for a course and log the action.

PostgreSQL stored procedure
CREATE OR REPLACE PROCEDURE enroll_student(
  p_student_id INTEGER,
  p_course_id  INTEGER,
  p_semester   TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
  -- ensure the combination does not already exist
  IF EXISTS (
    SELECT 1
    FROM enrollment
    WHERE student_id = p_student_id
      AND course_id  = p_course_id
      AND semester   = p_semester
  ) THEN
    RAISE EXCEPTION 'Student % already enrolled in course % for %',
      p_student_id, p_course_id, p_semester;
  END IF;

  INSERT INTO enrollment (student_id, course_id, semester, grade)
  VALUES (p_student_id, p_course_id, p_semester, NULL);

  INSERT INTO audit_log (action, details, created_at)
  VALUES ('enroll_student',
          FORMAT('student %s -> course %s (%s)', p_student_id, p_course_id, p_semester),
          NOW());
END;
$$;

Execute the procedure:

CALL enroll_student(3, 7, 'Spring 2026');

If any statement inside the procedure fails, PostgreSQL automatically rolls back the entire transaction unless you handle it explicitly.

User-Defined Function

A scalar function can compute helpful values such as a student's total enrolled credits in a given term.

Calculate term credit load
CREATE OR REPLACE FUNCTION term_credit_load(
  p_student_id INTEGER,
  p_semester   TEXT
)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_total INTEGER;
BEGIN
  SELECT COALESCE(SUM(c.credits), 0)
  INTO v_total
  FROM enrollment AS e
  JOIN course     AS c ON c.course_id = e.course_id
  WHERE e.student_id = p_student_id
    AND e.semester   = p_semester;

  RETURN v_total;
END;
$$;

Use it in queries:

SELECT student_id,
       term_credit_load(student_id, 'Spring 2025') AS credits
FROM student
ORDER BY credits DESC;

Handling Errors and Transactions

Stored procedures often coordinate multi-step changes. Add explicit transactions and exception blocks when needed.

Assign instructor with safeguards
CREATE OR REPLACE PROCEDURE assign_instructor(
  p_course_id     INTEGER,
  p_instructor_id INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
  PERFORM 1
  FROM instructor
  WHERE instructor_id = p_instructor_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Instructor % does not exist', p_instructor_id;
  END IF;

  UPDATE course
  SET instructor_id = p_instructor_id
  WHERE course_id = p_course_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Course % does not exist', p_course_id;
  END IF;
END;
$$;

When to Use Stored Routines

Centralize shared logic such as enrollment validation, prerequisite checks, or grade posting.
Reduce network chatter for multi-step operations that would otherwise require several round trips.
Enforce security by giving applications permission to execute a procedure without direct table access.

Limitations and cautions:

Stored procedures tie logic to a specific database engine; portability suffers.
Application code may be easier to version-control and test using standard tooling.
Overusing complex procedures can obscure data flow; balance database-side and application-side logic.

Summary

Stored procedures perform actions and are invoked with CALL/EXEC.
Functions return values and can be used in SELECT, WHERE, or ORDER BY.
Use parameters to pass context; raise exceptions when validation fails.
Prefer database-side routines for shared, sensitive, or performance-critical operations.

Quiz

Show quiz

Which statement invokes the enroll_student procedure for student 12?
A) SELECT enroll_student(12, 3, 'Fall 2025');
B) CALL enroll_student(12, 3, 'Fall 2025');
C) EXECUTE enroll_student;
D) RUN enroll_student(12, 3, 'Fall 2025');
What does term_credit_load return when a student has no enrollments in the semester?
A) NULL
B) 0
C) 'N/A'
D) An error
Why might you raise an exception inside a stored procedure?
A) To speed up execution
B) To abort the procedure when validation fails
C) To automatically log success
D) To refresh materialized views
True or False: Functions that make data modifications can be freely used inside SELECT statements in all databases.
A) True
B) False

Answers

Stored Procedure Example​

User-Defined Function​

Handling Errors and Transactions​

When to Use Stored Routines​

Summary​

Quiz​