Stored Procedures & Functions

What Are Stored Procedures and Functions?

Stored procedures and user-defined functions (UDFs) are named blocks of SQL (and procedural) code stored in the database. They encapsulate reusable logic so you don't repeat complex queries in every application.

	Stored Procedure	Function (UDF)
Returns value	Optional (OUT parameters)	Always returns a value
Call syntax	CALL proc()	Used in SELECT, WHERE
Side effects	Can INSERT/UPDATE/DELETE	Typically pure (no side effects)
Use in query	Cannot be used inline	Can be used in SELECT/WHERE

Note: Syntax varies significantly between databases. This chapter focuses on PostgreSQL (PL/pgSQL) and notes MySQL and SQL Server (T-SQL) differences.

Why Use Stored Procedures?

• Centralise business logic in the database (not scattered across application code)
• Security — grant EXECUTE on a procedure without granting table access
• Performance — execution plan is compiled and cached
• Reduce round trips — one call does multiple steps
• Consistent operations — all apps call the same procedure

User-Defined Functions

Scalar Function (returns one value)

PostgreSQL

-- Function: calculate monthly salary
CREATE OR REPLACE FUNCTION monthly_salary(annual NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT ROUND(annual / 12, 2);
$$;

-- Use like any built-in function
SELECT name, salary, monthly_salary(salary) AS monthly
FROM employees;

-- Function with conditional logic (PL/pgSQL)
CREATE OR REPLACE FUNCTION seniority_level(sal NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    IF sal >= 90000 THEN
        RETURN 'Senior';
    ELSIF sal >= 75000 THEN
        RETURN 'Mid-Level';
    ELSE
        RETURN 'Junior';
    END IF;
END;
$$;

-- Use in a query
SELECT name, salary, seniority_level(salary) AS seniority
FROM employees
ORDER BY salary DESC;

MySQL

DELIMITER //

CREATE FUNCTION monthly_salary(annual DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(annual / 12, 2);
END //

DELIMITER ;

-- Use it
SELECT name, salary, monthly_salary(salary) AS monthly FROM employees;

SQL Server (T-SQL)

CREATE FUNCTION dbo.monthly_salary(@annual DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN ROUND(@annual / 12, 2);
END;

-- Use it
SELECT name, salary, dbo.monthly_salary(salary) AS monthly FROM employees;

Table-Valued Function (returns a result set)

PostgreSQL

CREATE OR REPLACE FUNCTION get_dept_employees(dept TEXT)
RETURNS TABLE (
    emp_name  TEXT,
    salary    NUMERIC,
    hire_date TEXT
)
LANGUAGE sql
AS $$
    SELECT name, salary, hire_date
    FROM employees
    WHERE department = dept
    ORDER BY salary DESC;
$$;

-- Call it like a table
SELECT * FROM get_dept_employees('Finance');

-- Join it with other tables
SELECT e.emp_name, e.salary, d.budget
FROM get_dept_employees('Finance') e
JOIN departments d ON d.name = 'Finance';

Stored Procedures

Basic Procedure (PostgreSQL)

CREATE OR REPLACE PROCEDURE give_raise(dept TEXT, pct NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = ROUND(salary * (1 + pct / 100), 2)
    WHERE department = dept;
    
    RAISE NOTICE 'Gave %% raise to all % employees', pct, dept;
END;
$$;

-- Call the procedure
CALL give_raise('Finance', 10);
CALL give_raise('Technology', 8);

Procedure with OUT Parameter (return value)

CREATE OR REPLACE PROCEDURE get_dept_headcount(
    dept     TEXT,
    OUT count INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*) INTO count
    FROM employees
    WHERE department = dept;
END;
$$;

-- Call and capture the output
DO $$
DECLARE
    n INTEGER;
BEGIN
    CALL get_dept_headcount('Finance', n);
    RAISE NOTICE 'Finance headcount: %', n;
END;
$$;

MySQL Stored Procedure

DELIMITER //

CREATE PROCEDURE give_raise(
    IN dept_name VARCHAR(50),
    IN pct DECIMAL(5,2)
)
BEGIN
    UPDATE employees
    SET salary = ROUND(salary * (1 + pct / 100), 2)
    WHERE department = dept_name;
    
    SELECT CONCAT('Raise given to ', dept_name, ' team') AS message;
END //

DELIMITER ;

-- Call it
CALL give_raise('Technology', 10);

SQL Server Stored Procedure

CREATE PROCEDURE dbo.GiveRaise
    @DeptName  NVARCHAR(50),
    @Pct       DECIMAL(5,2)
AS
BEGIN
    UPDATE employees
    SET salary = ROUND(salary * (1 + @Pct / 100), 2)
    WHERE department = @DeptName;
    
    PRINT 'Raise applied to ' + @DeptName;
END;

-- Execute
EXEC dbo.GiveRaise @DeptName = 'Finance', @Pct = 10;

PL/pgSQL Control Flow (PostgreSQL)

Variables and Blocks

CREATE OR REPLACE FUNCTION salary_stats(dept TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_avg    NUMERIC;
    v_min    NUMERIC;
    v_max    NUMERIC;
    v_count  INTEGER;
    v_result TEXT;
BEGIN
    SELECT
        ROUND(AVG(salary), 0),
        MIN(salary),
        MAX(salary),
        COUNT(*)
    INTO v_avg, v_min, v_max, v_count
    FROM employees
    WHERE department = dept;

    v_result := dept || ': ' ||
                v_count || ' employees, avg ₹' || v_avg ||
                ', range ₹' || v_min || '–₹' || v_max;

    RETURN v_result;
END;
$$;

SELECT salary_stats('Finance');
-- Finance: 4 employees, avg ₹80000, range ₹69000–₹95000

IF / ELSIF / ELSE

CREATE OR REPLACE FUNCTION classify_employee(sal NUMERIC, yrs INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
    IF sal >= 90000 AND yrs >= 5 THEN
        RETURN 'Senior Principal';
    ELSIF sal >= 80000 AND yrs >= 3 THEN
        RETURN 'Senior';
    ELSIF sal >= 65000 THEN
        RETURN 'Mid-Level';
    ELSE
        RETURN 'Junior';
    END IF;
END;
$$;

LOOP / FOR LOOP

CREATE OR REPLACE PROCEDURE apply_tiered_raises()
LANGUAGE plpgsql
AS $$
DECLARE
    emp RECORD;
    v_raise NUMERIC;
BEGIN
    FOR emp IN SELECT id, name, salary FROM employees LOOP
        IF emp.salary >= 90000 THEN
            v_raise := 0.05;  -- 5% for seniors
        ELSIF emp.salary >= 75000 THEN
            v_raise := 0.08;  -- 8% for mid-level
        ELSE
            v_raise := 0.12;  -- 12% for juniors
        END IF;

        UPDATE employees
        SET salary = ROUND(salary * (1 + v_raise), 2)
        WHERE id = emp.id;

        RAISE NOTICE '% raised by %% (new: %)',
            emp.name,
            v_raise * 100,
            ROUND(emp.salary * (1 + v_raise), 2);
    END LOOP;
END;
$$;

CALL apply_tiered_raises();

EXCEPTION Handling

CREATE OR REPLACE PROCEDURE safe_insert_employee(
    p_name       TEXT,
    p_dept       TEXT,
    p_salary     NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees (name, department, salary, hire_date)
    VALUES (p_name, p_dept, p_salary, CURRENT_DATE);

    RAISE NOTICE 'Employee % added successfully', p_name;
EXCEPTION
    WHEN unique_violation THEN
        RAISE WARNING 'Employee with this email already exists';
    WHEN check_violation THEN
        RAISE WARNING 'Invalid salary: %', p_salary;
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Unexpected error: %', SQLERRM;
END;
$$;

Dropping Procedures and Functions

DROP PROCEDURE IF EXISTS give_raise(TEXT, NUMERIC);
DROP FUNCTION  IF EXISTS monthly_salary(NUMERIC);
-- PostgreSQL requires parameter types to identify the overload

Triggers — Automatic Procedure Calls

A trigger calls a procedure automatically when a table event occurs:

-- PostgreSQL: log salary changes
CREATE TABLE salary_audit (
    id          SERIAL PRIMARY KEY,
    employee_id INTEGER,
    old_salary  NUMERIC,
    new_salary  NUMERIC,
    changed_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Trigger function
CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.salary != NEW.salary THEN
        INSERT INTO salary_audit (employee_id, old_salary, new_salary)
        VALUES (OLD.id, OLD.salary, NEW.salary);
    END IF;
    RETURN NEW;
END;
$$;

-- Attach trigger to table
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

-- Now any UPDATE on salary auto-logs to salary_audit
UPDATE employees SET salary = 90000 WHERE id = 1;
SELECT * FROM salary_audit;

Practical Examples

Example 1: Monthly Payroll Summary Function

CREATE OR REPLACE FUNCTION payroll_summary(month_str TEXT)
RETURNS TABLE (
    department    TEXT,
    headcount     BIGINT,
    monthly_cost  NUMERIC
)
LANGUAGE sql
AS $$
    SELECT
        department,
        COUNT(*),
        ROUND(SUM(salary) / 12, 2)
    FROM employees
    GROUP BY department
    ORDER BY monthly_cost DESC;
$$;

SELECT * FROM payroll_summary('2026-06');

Example 2: Onboarding Procedure

CREATE OR REPLACE PROCEDURE onboard_employee(
    p_name       TEXT,
    p_dept       TEXT,
    p_salary     NUMERIC,
    p_manager_id INTEGER DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees (name, department, salary, hire_date, manager_id)
    VALUES (p_name, p_dept, p_salary, CURRENT_DATE, p_manager_id);

    -- Send welcome notification (placeholder)
    RAISE NOTICE 'Onboarded: % in % at ₹%', p_name, p_dept, p_salary;
END;
$$;

CALL onboard_employee('Nisha Gupta', 'Technology', 87000, 2);

Common Mistakes

1. Using procedures where a view is sufficient

If you just want to encapsulate a SELECT, use a view. Procedures are for logic with side effects.

2. Missing DELIMITER in MySQL

-- Wrong — semicolons inside the procedure end the CREATE prematurely
CREATE PROCEDURE test() BEGIN SELECT 1; END;

-- Correct
DELIMITER //
CREATE PROCEDURE test() BEGIN SELECT 1; END //
DELIMITER ;

3. Forgetting parameter types in DROP

-- PostgreSQL needs the signature to drop the right overload
DROP FUNCTION monthly_salary;          -- Error if overloaded
DROP FUNCTION monthly_salary(NUMERIC); -- Correct

Practice Exercises

1. Write a function that takes an employee name and returns their department and salary as a formatted string.
2. Create a stored procedure that transfers an employee from one department to another (takes employee id and new department name as parameters).
3. Write a procedure that gives a flat bonus of ₹5,000 to every employee in a given department.
4. Create a trigger that prevents an employee's salary from being reduced (reject any UPDATE that sets salary lower).
5. Write a table-valued function that returns all employees in a given salary range.

Summary

In this chapter you learned:

• Functions return a value and can be used in SELECT/WHERE
• Stored Procedures are called with CALL; may modify data; may have OUT parameters
• PL/pgSQL (PostgreSQL): DECLARE, BEGIN/END, IF/ELSIF/ELSE, FOR LOOP, EXCEPTION
• MySQL uses DELIMITER to separate procedure body from the outer semicolons
• SQL Server uses @variable syntax and EXEC
• Triggers call functions automatically on table events (INSERT, UPDATE, DELETE)
• CREATE OR REPLACE updates existing function/procedure safely
• Drop with full signature: DROP FUNCTION name(param_types)
• Use procedures for logic with side effects; views for reusable SELECT queries

Next up: Window Functions — advanced analytics without collapsing rows.