SQL Stored Procedures, Functions, and Triggers: Reusable SQL Logic, Automation, and When to Use Each

You write the same 30-line query every morning to generate a report. You copy-paste the same INSERT + UPDATE logic across 5 pipelines. You manually run a cleanup query after every data load. All of this can be automated with stored procedures, functions, and triggers.

A stored procedure is a saved block of SQL that you call by name — like a function in Python. A user-defined function returns a value you can use inside queries. A trigger runs automatically when data changes — insert, update, or delete.

Think of a stored procedure like a recipe saved in a cookbook. Instead of remembering every step (30 lines of SQL), you just say “make the chocolate cake” (EXEC sp_daily_report). The recipe executes every step in order. Functions are ingredients with preparation instructions — “diced onions” (always returns a value you use in a bigger recipe). Triggers are smoke detectors — they activate automatically when something happens (fire = data change).

Table of Contents

• Stored Procedures — Saved SQL Programs
• Creating and Executing Procedures
• Parameters: Input and Output
• Error Handling in Procedures (TRY/CATCH)
• Stored Procedure Best Practices
• User-Defined Functions (UDFs)
• Scalar Functions (Return One Value)
• Table-Valued Functions (Return a Table)
• Functions vs Procedures
• Triggers — Automatic Reactions to Data Changes
• AFTER Triggers
• INSTEAD OF Triggers
• Trigger Best Practices
• Real-World Patterns
• Common Mistakes
• Interview Questions
• Wrapping Up

Stored Procedures — Saved SQL Programs

Creating and Executing

-- Create a procedure
CREATE PROCEDURE usp_get_department_summary
AS
BEGIN
    SELECT department,
           COUNT(*) AS headcount,
           AVG(salary) AS avg_salary,
           SUM(salary) AS total_payroll
    FROM employees
    WHERE is_active = 1
    GROUP BY department
    ORDER BY total_payroll DESC;
END;

-- Execute it
EXEC usp_get_department_summary;

Parameters: Input and Output

-- Input parameters
CREATE PROCEDURE usp_get_employees_by_dept
    @department VARCHAR(50),
    @min_salary DECIMAL(10,2) = 0    -- Default value
AS
BEGIN
    SELECT emp_id, first_name, last_name, salary
    FROM employees
    WHERE department = @department AND salary >= @min_salary AND is_active = 1
    ORDER BY salary DESC;
END;

-- Call with parameters
EXEC usp_get_employees_by_dept @department = 'Engineering';
EXEC usp_get_employees_by_dept @department = 'Engineering', @min_salary = 90000;

-- Output parameters
CREATE PROCEDURE usp_get_headcount
    @department VARCHAR(50),
    @count INT OUTPUT
AS
BEGIN
    SELECT @count = COUNT(*) FROM employees
    WHERE department = @department AND is_active = 1;
END;

-- Call with output parameter
DECLARE @result INT;
EXEC usp_get_headcount @department = 'Engineering', @count = @result OUTPUT;
PRINT @result;    -- 3

Our Pipeline Logging Procedure (From the Blog)

-- The exact procedure we use in our ADF/Synapse pipelines
CREATE PROCEDURE usp_insert_pipeline_log
    @TableName      VARCHAR(100),
    @SchemaName     VARCHAR(50),
    @LoadType       VARCHAR(20),
    @RowsRead       INT,
    @RowsCopied     INT,
    @CopyDuration   INT,
    @Status         VARCHAR(20),
    @ErrorMessage   VARCHAR(500)
AS
BEGIN
    INSERT INTO PIPELINE_LOG
        (TableName, SchemaName, LoadType, RowsRead, RowsCopied,
         CopyDuration, Status, ErrorMessage, LoadDate)
    VALUES
        (@TableName, @SchemaName, @LoadType, @RowsRead, @RowsCopied,
         @CopyDuration, @Status, @ErrorMessage, GETDATE());
END;

Error Handling (TRY/CATCH)

CREATE PROCEDURE usp_safe_transfer
    @from_account INT,
    @to_account INT,
    @amount DECIMAL(10,2)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
            UPDATE accounts SET balance = balance - @amount WHERE account_id = @from_account;
            UPDATE accounts SET balance = balance + @amount WHERE account_id = @to_account;
        COMMIT TRANSACTION;
        PRINT 'Transfer successful';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT 'Transfer failed: ' + ERROR_MESSAGE();
        THROW;    -- Re-raise the error
    END CATCH;
END;

Stored Procedure Best Practices

• Prefix with usp_ (user stored procedure) — avoids confusion with system procedures (sp_)
• Always use SET NOCOUNT ON — prevents “N rows affected” messages that slow down applications
• Use TRY/CATCH for error handling
• Parameterize — never hardcode values inside procedures
• Keep them focused — one procedure, one purpose

User-Defined Functions (UDFs)

Scalar Functions (Return One Value)

-- Calculate annual salary
CREATE FUNCTION fn_annual_salary (@monthly_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @monthly_salary * 12;
END;

-- Use in SELECT
SELECT first_name, salary, dbo.fn_annual_salary(salary) AS annual FROM employees;

-- Calculate age from birth date
CREATE FUNCTION fn_calculate_age (@birth_date DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @birth_date, GETDATE())
        - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @birth_date, GETDATE()), @birth_date) > GETDATE()
               THEN 1 ELSE 0 END;
END;

-- Use in WHERE
SELECT * FROM customers WHERE dbo.fn_calculate_age(birth_date) >= 18;

Table-Valued Functions (Return a Table)

-- Inline table-valued function
CREATE FUNCTION fn_employees_by_salary_range (
    @min_salary DECIMAL(10,2),
    @max_salary DECIMAL(10,2)
)
RETURNS TABLE
AS
RETURN (
    SELECT emp_id, first_name, last_name, salary, department
    FROM employees
    WHERE salary BETWEEN @min_salary AND @max_salary AND is_active = 1
);

-- Use like a table (in FROM clause)
SELECT * FROM fn_employees_by_salary_range(80000, 100000);

-- Join with other tables
SELECT e.*, d.budget
FROM fn_employees_by_salary_range(80000, 100000) e
JOIN departments d ON e.department = d.dept_name;

Functions vs Procedures

Feature	Stored Procedure	Function
Returns	Result sets, output parameters	Single value (scalar) or table
Use in SELECT	❌ No	✅ Yes (SELECT dbo.fn_age(date))
Use in WHERE	❌ No	✅ Yes (WHERE dbo.fn_age(date) > 18)
Modify data	✅ Yes (INSERT, UPDATE, DELETE)	❌ No (read-only)
Transaction control	✅ Yes (BEGIN/COMMIT/ROLLBACK)	❌ No
Error handling	✅ TRY/CATCH	❌ Limited
Call syntax	EXEC usp_name	SELECT dbo.fn_name()
Best for	Business logic, ETL, automation	Calculations, transformations

The rule: If it modifies data → Procedure. If it calculates and returns a value → Function.

Triggers — Automatic Reactions to Data Changes

A trigger is SQL code that runs AUTOMATICALLY when an INSERT, UPDATE, or DELETE happens on a table.

AFTER Triggers

Run AFTER the operation completes:

-- Log every salary change
CREATE TRIGGER trg_salary_audit
ON employees
AFTER UPDATE
AS
BEGIN
    IF UPDATE(salary)
    BEGIN
        INSERT INTO salary_audit_log (emp_id, old_salary, new_salary, changed_at, changed_by)
        SELECT i.emp_id, d.salary, i.salary, GETDATE(), SYSTEM_USER
        FROM inserted i
        JOIN deleted d ON i.emp_id = d.emp_id
        WHERE i.salary != d.salary;
    END;
END;

-- Now any UPDATE to salary automatically logs the change
UPDATE employees SET salary = 110000 WHERE emp_id = 1001;
-- salary_audit_log now has: emp_id=1001, old=105000, new=110000, changed_at=now

Special tables in triggers: – inserted — new values (for INSERT and UPDATE) – deleted — old values (for DELETE and UPDATE)

INSTEAD OF Triggers

Replace the original operation entirely:

-- Prevent deletes, soft-delete instead
CREATE TRIGGER trg_soft_delete
ON employees
INSTEAD OF DELETE
AS
BEGIN
    UPDATE employees
    SET is_active = 0, deleted_at = GETDATE()
    WHERE emp_id IN (SELECT emp_id FROM deleted);

    PRINT 'Employee soft-deleted (not physically removed)';
END;

-- DELETE now does a soft delete instead
DELETE FROM employees WHERE emp_id = 1005;
-- Row still exists, but is_active = 0, deleted_at = timestamp

Trigger Best Practices

• Keep triggers lightweight — heavy triggers slow down every INSERT/UPDATE/DELETE
• Avoid triggers that call other triggers (cascading triggers — debugging nightmare)
• Use for auditing and validation — not for complex business logic
• Document triggers — they are invisible in the application code
• Test with multi-row operations — triggers fire once per statement, not once per row

Real-World Patterns

Pattern 1: Pipeline Audit Logging (Our Blog Pattern)

-- Used in every ADF/Synapse pipeline
EXEC usp_insert_pipeline_log
    @TableName = 'Customer',
    @SchemaName = 'SalesLT',
    @LoadType = 'FULL',
    @RowsRead = 847,
    @RowsCopied = 847,
    @CopyDuration = 12,
    @Status = 'SUCCESS',
    @ErrorMessage = 'NA';

Pattern 2: Watermark Update (Incremental Load)

CREATE PROCEDURE usp_update_watermark
    @TableName VARCHAR(100),
    @LastLoadedValue VARCHAR(100)
AS
BEGIN
    UPDATE CONFIGTABLE_V2
    SET LastLoadedValue = @LastLoadedValue
    WHERE TableName = @TableName;
END;

Pattern 3: Automatic Audit Trail (Trigger)

CREATE TRIGGER trg_employee_changes
ON employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT INTO employee_audit (action, emp_id, details, action_date)
    SELECT 'INSERT', emp_id, CONCAT('New employee: ', first_name), GETDATE() FROM inserted
    WHERE NOT EXISTS (SELECT 1 FROM deleted WHERE deleted.emp_id = inserted.emp_id)
    UNION ALL
    SELECT 'DELETE', emp_id, CONCAT('Removed: ', first_name), GETDATE() FROM deleted
    WHERE NOT EXISTS (SELECT 1 FROM inserted WHERE inserted.emp_id = deleted.emp_id)
    UNION ALL
    SELECT 'UPDATE', i.emp_id, CONCAT('Updated: ', i.first_name), GETDATE()
    FROM inserted i JOIN deleted d ON i.emp_id = d.emp_id;
END;

Common Mistakes

1. Heavy logic in triggers — triggers run on EVERY write operation. A trigger that takes 2 seconds means every INSERT takes 2 extra seconds. Keep triggers lightweight.

2. Forgetting SET NOCOUNT ON — without it, procedures return “N rows affected” messages that slow down applications and confuse result processing.

3. Scalar functions in WHERE clause — WHERE dbo.fn_age(birth_date) > 18 runs the function for EVERY row, killing performance. Use inline expressions when possible.

4. Not handling NULLs in function parameters — functions crash if passed NULL without explicit handling. Add IF @param IS NULL RETURN NULL at the top.

5. Cascading triggers — trigger A fires trigger B which fires trigger C. Nearly impossible to debug. Disable nested triggers if not needed.

Interview Questions

Q: What is the difference between a stored procedure and a function? A: Procedures can modify data (INSERT/UPDATE/DELETE), use transactions, and are called with EXEC. Functions return values, can be used in SELECT/WHERE clauses, but cannot modify data. Use procedures for business logic and ETL. Use functions for calculations.

Q: What is a trigger and when should you use one? A: A trigger is SQL code that executes automatically on INSERT, UPDATE, or DELETE. Use for audit logging (track who changed what), data validation (prevent invalid changes), and soft deletes (replace DELETE with UPDATE is_active=0). Keep triggers lightweight to avoid slowing down write operations.

Q: What are the inserted and deleted tables in triggers? A: Special temporary tables available inside triggers. inserted contains the new values (for INSERT and UPDATE). deleted contains the old values (for DELETE and UPDATE). For an UPDATE, comparing inserted and deleted shows what changed.

Wrapping Up

Stored procedures automate repetitive SQL logic. Functions calculate values reusable in queries. Triggers react to data changes automatically. Together, they turn your database from a passive data store into an active, self-auditing, self-validating system.

Related posts: – SQL DDL, DML, Constraints – Unified Pipeline (uses stored procedures) – Audit Logging in Pipelines

---

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.