SQL TL;DR: Stored Procedures & Functions

Stored Procedures & Functions

/tldr: Pre-compiled code blocks for business logic and performance.

Programmability Performance Business Logic

THE CORE IDEA: Reusable, Pre-compiled Code

Stored procedures and functions (sometimes collectively called routines) are blocks of T-SQL (or PL/SQL, etc.) stored directly in the database. They allow complex, multi-step business logic to be executed with a single call, ensuring consistency and improved performance through pre-compilation.

1. Stored Procedure vs. Function

Stored Procedure

**DML Allowed:** Can perform data modification (INSERT, UPDATE, DELETE).
**Returns:** Can return zero or one result sets, and can use OUT parameters.
**Execution:** Called using EXEC or CALL. Cannot be used directly in a SELECT statement.
**Autonomy:** Can execute transactional control commands (COMMIT, ROLLBACK).

-- Example: Transfer funds (modifies data)
CREATE PROCEDURE transfer_funds (
    @from_id INT, 
    @to_id INT, 
    @amount DECIMAL
)
AS
BEGIN
    UPDATE accounts SET balance = balance - @amount WHERE account_id = @from_id;
    UPDATE accounts SET balance = balance + @amount WHERE account_id = @to_id;
    COMMIT;
END;

User-Defined Function (UDF)

**DML Forbidden:** Cannot perform data modification; strictly read-only.
**Returns:** Must return a single scalar value or a whole table (Table-Valued Function).
**Execution:** Can be used directly within a SELECT statement, just like built-in functions (SUM()).
**Autonomy:** Cannot execute transactional control commands.

-- Example: Calculate tax (reads data, returns value)
CREATE FUNCTION calculate_tax (
    @income DECIMAL, 
    @rate DECIMAL
)
RETURNS DECIMAL
AS
BEGIN
    RETURN @income * @rate;
END;

-- Usage in SELECT
SELECT name, dbo.calculate_tax(salary, 0.15) AS tax_amount FROM employees;

2. Core Benefits & Security

Performance

They are pre-compiled and optimized by the database engine, reducing the parsing and query planning time on every call. This is highly beneficial for frequently executed logic.

Security (Abstraction)

Users can be granted permission to execute a procedure without having direct permissions on the underlying tables. This protects the data structure and prevents SQL Injection against the raw tables.

Modularity & Consistency

Centralizes business rules. If a rule changes (e.g., how shipping cost is calculated), you only update the code in one place (the procedure/function), ensuring all applications use the same, correct logic.

3. Variables, Parameters, & Return Types

Both procedures and functions use input parameters. Procedures can also utilize **OUTPUT** parameters to return multiple values.

-- Variable Declaration (often starts with @)
DECLARE @total_count INT;
SET @total_count = (SELECT COUNT(*) FROM products);

-- Procedure with OUTPUT Parameter (Allows returning multiple pieces of data)
CREATE PROCEDURE get_stats (
    @category_id INT, 
    @count INT OUTPUT,
    @average_price DECIMAL OUTPUT
)
AS
BEGIN
    SELECT @count = COUNT(*), @average_price = AVG(price) 
    FROM products 
    WHERE category_id = @category_id;
END;

Keep the logic in the layer that controls the data.