SQL Stored Procedures, Functions, and Triggers

1. What is a Stored Procedure?

A stored procedure is a prepared collection of one or more SQL statements compiled and stored directly inside the database data catalog. Instead of sending repetitive raw queries across the network from client applications, developers invoke the procedure by name.

Stored procedures provide distinct architectural advantages:

Precompiled Performance: The database engine parses, optimizes, and compiles the execution plan on initial creation. Subsequent calls execute the precompiled plan immediately.
Reduced Network Traffic: A single procedure invocation replaces dozens of back-and-forth network round trips between application servers and the database.
Access Control and Security: Administrators can grant users permission to execute specific procedures without giving them direct read or write access to underlying tables.
Business Logic Centralization: Complex business rules (such as financial reconciliations or order fulfillment routines) live in one authoritative place.

`Architecture: Client Queries vs Stored Procedures`

Raw Queries:App sends multi-line SQL -> Engine parses -> Engine optimizes -> Engine executes -> Network latency on every step.
Stored Procedure:App sends CALL ProcessOrder(101) -> Engine executes pre-compiled plan -> Single response payload.

2. CREATE PROCEDURE

The CREATE PROCEDURE statement defines a new stored procedure. Procedural syntax varies slightly across major enterprise database engines:

MySQL Syntax

DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
SELECT id, name, salary FROM employees ORDER BY salary DESC;
END //
DELIMITER ;

PostgreSQL Syntax (PL/pgSQL)

CREATE OR REPLACE PROCEDURE GiveAnnualRaise(dept_id INT, raise_pct NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = salary * (1 + raise_pct / 100)
    WHERE department_id = dept_id;
END;
$$;

Microsoft SQL Server (T-SQL)

CREATE PROCEDURE GetDepartmentHeadcount
    @DeptId INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COUNT(*) AS Headcount FROM employees WHERE department_id = @DeptId;
END;

3. Stored Procedure with Parameters (IN, OUT, INOUT)

Parameters enable stored procedures to accept dynamic inputs and return output values back to the calling client. SQL standardizes three parameter modes:

IN (Default): Passes data into the procedure. The procedure can inspect the value but modifications do not affect caller variables.
OUT: Passes an uninitialized reference into the procedure. The procedure populates this parameter, returning the value to the caller upon completion.
INOUT: Acts as both input and output. The caller passes an initial value, the procedure inspects and updates it, and returns the modified result.

-- MySQL procedure demonstrating IN and OUT parameters
DELIMITER //
CREATE PROCEDURE CalculateDeptPayroll(
    IN p_dept_id INT,
    OUT p_total_payroll INT,
    OUT p_avg_salary DECIMAL(10,2)
)
BEGIN
    SELECT SUM(salary), AVG(salary)
    INTO p_total_payroll, p_avg_salary
    FROM employees
    WHERE department_id = p_dept_id;
END //
DELIMITER ;

4. Executing a Stored Procedure (EXEC, CALL)

Database engines provide dedicated command verbs to invoke stored procedures:

CALL: Standard syntax used in MySQL, PostgreSQL 11+, and Oracle.
EXEC / EXECUTE: Standard syntax used in Microsoft SQL Server.

-- MySQL execution with session variables to capture OUT parameters
CALL CalculateDeptPayroll(1, @total, @average);
SELECT @total AS TotalPayroll, @average AS AverageSalary;

-- SQL Server execution
EXEC GetDepartmentHeadcount @DeptId = 1;

-- PostgreSQL execution
CALL GiveAnnualRaise(1, 5.0);

`Session Variable Retrieval`

Capture Outputs:When calling procedures with OUT parameters in MySQL, pass session variables prefixed with @ to capture outputs.
Print or Query:Execute a SELECT query against the session variables to inspect the returned values.

5. User-Defined Functions (UDF)

A User-Defined Function (UDF) is a reusable programmatic routine designed to calculate and return values. UDFs fall into two major categories:

Scalar Functions: Accept zero or more arguments and return a single atomic value (such as a string, number, or boolean). Because they return single values, scalar functions can appear inside SELECT column lists, WHERE clauses, and ORDER BY expressions.
Table-Valued Functions (TVF): Return an entire table result set. They are referenced inside the FROM clause of a query and can participate in joins.

-- PostgreSQL scalar function calculating tax on salary
CREATE OR REPLACE FUNCTION CalculateTax(salary NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
RETURN salary * 0.22;
END;
$$ LANGUAGE plpgsql;

-- Using the UDF directly in a SELECT statement
SELECT name, salary, CalculateTax(salary) AS estimated_tax FROM employees;

6. Stored Procedure vs Function

While both objects store procedural logic on the server, their capabilities and execution contexts differ fundamentally:

Feature	Stored Procedure	User-Defined Function
Return Value	Optional (returns 0, 1, or multiple result sets/OUT params)	Mandatory (must return single value or table)
Invocation Method	Called independently via CALL or EXEC	Embedded inside SELECT, WHERE, HAVING clauses
Data Modification (DML)	Permits INSERT, UPDATE, DELETE operations	Generally read-only (side-effects disallowed)
Transaction Control	Can manage transactions (COMMIT, ROLLBACK)	Cannot initiate or commit transactions
Calling the Other	Can call functions freely	Cannot execute stored procedures

7. Variables in SQL

Variables store intermediate values in memory during script or procedure execution.

T-SQL (SQL Server): Variables are prefixed with @ and declared with the DECLARE keyword.
PL/pgSQL (PostgreSQL): Variables are declared in a dedicated DECLARE section prior to the BEGIN block.
MySQL: User-defined session variables use @name, while procedure-scoped local variables are created using DECLARE name datatype.

-- T-SQL Variable Example
DECLARE @MinSalary INT = 80000;
DECLARE @HighEarnerCount INT;

SELECT @HighEarnerCount = COUNT(*) FROM employees WHERE salary >= @MinSalary;
PRINT @HighEarnerCount;

8. IF ELSE and WHILE Loops in SQL

Procedural extensions enable branching and loops within stored routines:

Conditional Branching (IF...ELSE)

-- T-SQL Conditional Statement
IF (SELECT AVG(salary) FROM employees) > 100000
BEGIN
PRINT 'Executive payroll threshold met.';
END
ELSE
BEGIN
PRINT 'Standard payroll bracket.';
END;

Looping with WHILE

-- MySQL WHILE loop syntax
DECLARE counter INT DEFAULT 1;
WHILE counter <= 5 DO
INSERT INTO log_entries (step_number) VALUES (counter);
SET counter = counter + 1;
END WHILE;

`Performance Warning: Set-Based SQL vs Loops`

Set-Based Preference:SQL engines are built for set-based operations. A single UPDATE or INSERT query is often 100x faster than a procedural loop.
Reserve Loops For:Batching massive multi-million row deletes to prevent log file saturation or invoking external webhooks.

9. What is a Trigger in SQL?

A trigger is a special type of stored procedure that fires automatically when a specific database event occurs on a table or view. Unlike ordinary stored procedures, triggers cannot be invoked manually using CALL or EXEC.

Common use cases for triggers include:

Automated Audit Trails: Recording historical changes whenever confidential records are updated or deleted.
Data Integrity Enforcement: Preventing invalid status transitions (such as transitioning an order from 'delivered' back to 'pending').
Cross-Table Synchronization: Automatically updating inventory stock levels when a customer places an order.

Inside a trigger body, the database provides two special virtual tables (or pseudo-records):

OLD: Contains the column values as they existed before the update or delete operation.
NEW: Contains the prospective column values after the insert or update operation.

10. CREATE TRIGGER (BEFORE, AFTER, INSTEAD OF)

Triggers are categorized by their execution timing:

BEFORE Trigger: Fires prior to applying the data modification. Used for input sanitation, default calculations, and rejecting transactions before disk writes.
AFTER Trigger: Fires immediately after the data modification succeeds. Used for audit logging, sending notifications, and updating summary tables.
INSTEAD OF Trigger: Intercepts the statement and executes custom procedural code in place of the default operation. Primarily used to enable updates on complex non-updatable views.

Automated Audit Trail with AFTER UPDATE Trigger

SQL

Capture previous and new order statuses automatically on every update

Triggers can also filter events using conditional clauses such as WHEN OLD.salary != NEW.salary to prevent redundant trigger executions:

Conditional Trigger with Salary Difference Tracking

SQL

Fire only when salary value changes and compute compensation differentials

`Managing Triggers Cleanly`

Dropping a Trigger:Use DROP TRIGGER IF EXISTS trigger_name; to remove a trigger cleanly.
Hidden Side Effects:Too many cascading triggers make debugging difficult; document all automated mutations thoroughly.

11. Cursors in SQL

A cursor is a database pointer that enables procedural code to process a query result set row by row, rather than processing the entire set at once.

A cursor follows a strict lifecycle:

DECLARE: Defines the cursor name and its associated SELECT query.
OPEN: Executes the query and populates the cursor workspace.
FETCH NEXT: Retrieves the next row from the cursor into local variables.
CLOSE: Releases the cursor result set.
DEALLOCATE: Removes the cursor reference from memory.

-- T-SQL Cursor Lifecycle
DECLARE @EmpName NVARCHAR(50), @EmpSal INT;
DECLARE emp_cursor CURSOR FOR
    SELECT name, salary FROM employees;

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @EmpName, @EmpSal;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee: ' + @EmpName;
    FETCH NEXT FROM emp_cursor INTO @EmpName, @EmpSal;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

`RBAR: Row By Agonizing Row`

Performance Penalty:Cursors bypass relational optimization and force thousands of repetitive context switches.
Best Practice:Always explore window functions, CTEs, or set-based UPDATE statements before resorting to a cursor.

12. Dynamic SQL

Dynamic SQL refers to constructing SQL statements as text strings at runtime and passing them to the database engine for execution.

Dynamic SQL is required when query parameters cannot be represented as standard literal values, such as dynamic table names, dynamic column lists in pivoting, or complex search filters with dozens of optional checkboxes.

Safe Dynamic SQL with Parameterization

-- SQL Server parameterized dynamic SQL using sp_executesql
DECLARE @SQL NVARCHAR(MAX);
DECLARE @MinSalary INT = 90000;

SET @SQL = N'SELECT id, name, salary FROM employees WHERE salary >= @Threshold';

EXEC sp_executesql
    @stmt = @SQL,
    @params = N'@Threshold INT',
    @Threshold = @MinSalary;

`Security Alert: SQL Injection Prevention`

Dangerous Concatenation:Never concatenate raw user input into dynamic SQL strings: SET @sql = 'SELECT * FROM users WHERE name = '' + @userInput.
Parameterize Everything:Always use sp_executesql or PREPARE / EXECUTE statements with bound variables to prevent SQL injection.

Knowledge Check: Stored Procedures, Functions & Triggers

1. How is a stored procedure invoked in standard SQL?

2. What can a stored procedure do that a function cannot?

3. Which pseudo-record contains post-modification values in triggers?

4. When does an AFTER trigger execute?

5. Why are cursors generally discouraged in production queries?

6. What is the primary security risk of dynamic SQL?

Previous Next