Tsql

T-SQL Core Knowledge

Full Reference: See advanced.md for multi-statement TVFs, custom error messages, INSTEAD OF triggers, transaction isolation levels, cursors, dynamic SQL, and recursive CTEs.

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: sqlserver for comprehensive documentation.

Basic Structure

-- Anonymous block
BEGIN
    DECLARE @count INT = 0;
    SET @count = @count + 1;
    PRINT 'Count: ' + CAST(@count AS VARCHAR);
END;
GO

Variables

DECLARE @name VARCHAR(100) = 'John';
DECLARE @age INT;
DECLARE @salary DECIMAL(10,2), @bonus DECIMAL(10,2);

SET @age = 25;
SELECT @salary = salary FROM employees WHERE id = 1;

-- Multiple assignments
SELECT @salary = salary, @bonus = bonus
FROM employees WHERE id = 1;

-- Table variable
DECLARE @employees TABLE (
    id INT,
    name VARCHAR(100),
    salary DECIMAL(10,2)
);

INSERT INTO @employees SELECT id, name, salary FROM employees;

Stored Procedures

Basic Procedure

CREATE OR ALTER PROCEDURE usp_GetEmployee
    @EmployeeId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE employee_id = @EmployeeId;
END;
GO

-- Execute
EXEC usp_GetEmployee @EmployeeId = 100;
-- or
EXEC usp_GetEmployee 100;

Procedure with OUTPUT Parameters

CREATE OR ALTER PROCEDURE usp_GetEmployeeStats
    @DeptId INT,
    @EmployeeCount INT OUTPUT,
    @TotalSalary DECIMAL(15,2) OUTPUT,
    @AvgSalary DECIMAL(15,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        @EmployeeCount = COUNT(*),
        @TotalSalary = SUM(salary),
        @AvgSalary = AVG(salary)
    FROM employees
    WHERE department_id = @DeptId;
END;
GO

-- Call with OUTPUT
DECLARE @Count INT, @Total DECIMAL(15,2), @Avg DECIMAL(15,2);
EXEC usp_GetEmployeeStats
    @DeptId = 10,
    @EmployeeCount = @Count OUTPUT,
    @TotalSalary = @Total OUTPUT,
    @AvgSalary = @Avg OUTPUT;

PRINT 'Count: ' + CAST(@Count AS VARCHAR);

Procedure with Return Value

CREATE OR ALTER PROCEDURE usp_ValidateEmployee
    @EmployeeId INT
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM employees WHERE employee_id = @EmployeeId)
        RETURN -1;  -- Not found

    IF EXISTS (SELECT 1 FROM employees WHERE employee_id = @EmployeeId AND status = 'INACTIVE')
        RETURN -2;  -- Inactive

    RETURN 0;  -- Success
END;
GO

-- Check return value
DECLARE @result INT;
EXEC @result = usp_ValidateEmployee @EmployeeId = 100;

IF @result = 0
    PRINT 'Valid';
ELSE IF @result = -1
    PRINT 'Employee not found';

Functions

Scalar Function

CREATE OR ALTER FUNCTION dbo.fn_CalculateBonus(
    @Salary DECIMAL(10,2),
    @YearsOfService INT
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @Bonus DECIMAL(10,2);

    SET @Bonus = CASE
        WHEN @YearsOfService >= 10 THEN @Salary * 0.15
        WHEN @YearsOfService >= 5 THEN @Salary * 0.10
        ELSE @Salary * 0.05
    END;

    RETURN @Bonus;
END;
GO

-- Usage
SELECT employee_id, salary,
    dbo.fn_CalculateBonus(salary, years_of_service) AS bonus
FROM employees;

Inline Table-Valued Function

CREATE OR ALTER FUNCTION dbo.fn_GetDeptEmployees(
    @DeptId INT
)
RETURNS TABLE
AS
RETURN (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = @DeptId
);
GO

-- Usage
SELECT * FROM dbo.fn_GetDeptEmployees(10);

Control Flow

IF...ELSE

DECLARE @status VARCHAR(20);

IF @status = 'ACTIVE'
BEGIN
    PRINT 'User is active';
    -- Multiple statements in BEGIN...END
END
ELSE IF @status = 'PENDING'
    PRINT 'User is pending';  -- Single statement, no BEGIN needed
ELSE
BEGIN
    PRINT 'User is inactive';
END;

CASE Expression

SELECT
    employee_id,
    salary,
    CASE
        WHEN salary >= 100000 THEN 'Executive'
        WHEN salary >= 50000 THEN 'Senior'
        WHEN salary >= 30000 THEN 'Mid'
        ELSE 'Junior'
    END AS level
FROM employees;

-- Simple CASE
SELECT
    employee_id,
    CASE status
        WHEN 'A' THEN 'Active'
        WHEN 'I' THEN 'Inactive'
        ELSE 'Unknown'
    END AS status_name
FROM employees;

WHILE Loop

DECLARE @counter INT = 1;

WHILE @counter <= 10
BEGIN
    PRINT 'Counter: ' + CAST(@counter AS VARCHAR);
    SET @counter = @counter + 1;

    IF @counter = 5
        CONTINUE;  -- Skip to next iteration

    IF @counter = 8
        BREAK;  -- Exit loop
END;

Error Handling

TRY...CATCH

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Error information
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    -- Re-throw or log
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

Error Functions

THROW vs RAISERROR

-- THROW (SQL Server 2012+, preferred)
THROW 50001, 'Custom error message', 1;

-- THROW without parameters re-throws current error
BEGIN CATCH
    INSERT INTO error_log (message, error_time)
    VALUES (ERROR_MESSAGE(), GETDATE());

    THROW;  -- Re-throw original error
END CATCH;

-- RAISERROR (legacy)
RAISERROR('Error: %s', 16, 1, @ErrorMessage);

Triggers

DML Trigger

CREATE OR ALTER TRIGGER tr_employees_audit
ON employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Handle INSERT
    IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    BEGIN
        INSERT INTO employees_audit (action, employee_id, new_salary, changed_by, changed_at)
        SELECT 'INSERT', employee_id, salary, SYSTEM_USER, GETDATE()
        FROM inserted;
    END

    -- Handle UPDATE
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        INSERT INTO employees_audit (action, employee_id, old_salary, new_salary, changed_by, changed_at)
        SELECT 'UPDATE', i.employee_id, d.salary, i.salary, SYSTEM_USER, GETDATE()
        FROM inserted i
        INNER JOIN deleted d ON i.employee_id = d.employee_id;
    END

    -- Handle DELETE
    IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        INSERT INTO employees_audit (action, employee_id, old_salary, changed_by, changed_at)
        SELECT 'DELETE', employee_id, salary, SYSTEM_USER, GETDATE()
        FROM deleted;
    END
END;
GO

Transactions

BEGIN TRANSACTION;
-- or
BEGIN TRAN;

SAVE TRANSACTION SavePoint1;

-- Rollback to savepoint
ROLLBACK TRANSACTION SavePoint1;

COMMIT TRANSACTION;
-- or
COMMIT;

-- Check transaction count
SELECT @@TRANCOUNT;

-- Named transaction
BEGIN TRANSACTION MyTransaction;
COMMIT TRANSACTION MyTransaction;

Common Table Expressions (CTE)

;WITH dept_stats AS (
    SELECT
        department_id,
        COUNT(*) AS emp_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name, ds.emp_count, ds.avg_salary
FROM departments d
INNER JOIN dept_stats ds ON d.department_id = ds.department_id;

Best Practices

DO

• Use SET NOCOUNT ON in procedures
• Use TRY...CATCH for error handling
• Use sp_executesql for dynamic SQL
• Use FAST_FORWARD cursors when possible
• Use table-valued parameters for batch operations
• Always qualify object names with schema

DON'T

• Use SELECT * in production code
• Build dynamic SQL with string concatenation
• Use cursors when set-based operations work
• Ignore error handling
• Use deprecated features (GROUP BY ALL, etc.)

When NOT to Use This Skill

• Basic SQL Server SQL - Use sqlserver skill for data types, indexes, temporal tables
• PL/pgSQL (PostgreSQL) - Use plpgsql skill for PostgreSQL procedures
• PL/SQL (Oracle) - Use plsql skill for Oracle procedures
• Basic SQL - Use sql-fundamentals for ANSI SQL basics

Anti-Patterns

Quick Troubleshooting

Reference Documentation

• Procedures
• Functions
• Error Handling

T-SQL Advanced Patterns

Multi-Statement Table-Valued Functions

CREATE OR ALTER FUNCTION dbo.fn_GetEmployeeHierarchy(
    @ManagerId INT
)
RETURNS @result TABLE (
    employee_id INT,
    name VARCHAR(200),
    level INT
)
AS
BEGIN
    ;WITH hierarchy AS (
        SELECT employee_id, first_name + ' ' + last_name AS name, 0 AS level
        FROM employees WHERE employee_id = @ManagerId

        UNION ALL

        SELECT e.employee_id, e.first_name + ' ' + e.last_name, h.level + 1
        FROM employees e
        INNER JOIN hierarchy h ON e.manager_id = h.employee_id
    )
    INSERT INTO @result
    SELECT employee_id, name, level FROM hierarchy;

    RETURN;
END;
GO

---

Advanced Error Handling

Custom Error Messages

-- Add message to sys.messages
EXEC sp_addmessage
    @msgnum = 50001,
    @severity = 16,
    @msgtext = 'Employee %d not found in department %s';

-- Use message
RAISERROR(50001, 16, 1, @EmployeeId, @DeptName);

-- Remove message
EXEC sp_dropmessage @msgnum = 50001;

THROW vs RAISERROR

-- THROW (SQL Server 2012+, preferred)
THROW 50001, 'Custom error message', 1;

-- THROW without parameters re-throws current error
BEGIN CATCH
    -- Log error
    INSERT INTO error_log (message, error_time)
    VALUES (ERROR_MESSAGE(), GETDATE());

    THROW;  -- Re-throw original error
END CATCH;

-- RAISERROR (legacy)
RAISERROR('Error: %s', 16, 1, @ErrorMessage);

---

Advanced Triggers

INSTEAD OF Trigger

CREATE OR ALTER TRIGGER tr_employees_view_insert
ON v_employees
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO employees (first_name, last_name, email, department_id)
    SELECT first_name, last_name, email, department_id
    FROM inserted;
END;
GO

Trigger with UPDATE()

CREATE OR ALTER TRIGGER tr_employees_salary_check
ON employees
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF UPDATE(salary)  -- Check if salary column was updated
    BEGIN
        IF EXISTS (
            SELECT 1 FROM inserted i
            INNER JOIN deleted d ON i.employee_id = d.employee_id
            WHERE i.salary > d.salary * 2
        )
        BEGIN
            RAISERROR('Salary increase cannot exceed 100%%', 16, 1);
            ROLLBACK TRANSACTION;
        END
    END
END;
GO

---

Transaction Isolation Levels

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- Default
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;  -- Requires DB option

---

Cursors

DECLARE @emp_id INT, @name VARCHAR(100), @salary DECIMAL(10,2);

DECLARE emp_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE department_id = 10;

OPEN emp_cursor;

FETCH NEXT FROM emp_cursor INTO @emp_id, @name, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @name + ': ' + CAST(@salary AS VARCHAR);
    FETCH NEXT FROM emp_cursor INTO @emp_id, @name, @salary;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Cursor Options

---

Dynamic SQL

-- EXEC with string (SQL injection risk!)
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM employees WHERE department_id = ' + CAST(@DeptId AS NVARCHAR);
EXEC(@sql);

-- sp_executesql (preferred, parameterized)
DECLARE @sql NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);

SET @sql = N'SELECT * FROM employees WHERE department_id = @DeptId AND salary > @MinSalary';
SET @params = N'@DeptId INT, @MinSalary DECIMAL(10,2)';

EXEC sp_executesql @sql, @params, @DeptId = 10, @MinSalary = 50000;

-- With OUTPUT
DECLARE @count INT;
SET @sql = N'SELECT @cnt = COUNT(*) FROM employees WHERE department_id = @DeptId';
SET @params = N'@DeptId INT, @cnt INT OUTPUT';

EXEC sp_executesql @sql, @params, @DeptId = 10, @cnt = @count OUTPUT;
PRINT @count;

---

Common Table Expressions (CTE)

;WITH dept_stats AS (
    SELECT
        department_id,
        COUNT(*) AS emp_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name, ds.emp_count, ds.avg_salary
FROM departments d
INNER JOIN dept_stats ds ON d.department_id = ds.department_id;

Recursive CTE

;WITH hierarchy AS (
    SELECT employee_id, first_name, manager_id, 0 AS level
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.first_name, e.manager_id, h.level + 1
    FROM employees e
    INNER JOIN hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM hierarchy;

---

Transactions with Savepoints

BEGIN TRANSACTION;
-- or
BEGIN TRAN;

SAVE TRANSACTION SavePoint1;

-- Rollback to savepoint
ROLLBACK TRANSACTION SavePoint1;

COMMIT TRANSACTION;
-- or
COMMIT;

-- Check transaction count
SELECT @@TRANCOUNT;

-- Named transaction
BEGIN TRANSACTION MyTransaction;
COMMIT TRANSACTION MyTransaction;

---

Control Flow

GOTO

DECLARE @value INT = 5;

IF @value < 0
    GOTO NegativeValue;

PRINT 'Value is positive';
GOTO EndBlock;

NegativeValue:
PRINT 'Value is negative';

EndBlock:
PRINT 'Done';

WHILE with BREAK/CONTINUE

DECLARE @counter INT = 1;

WHILE @counter <= 10
BEGIN
    PRINT 'Counter: ' + CAST(@counter AS VARCHAR);
    SET @counter = @counter + 1;

    IF @counter = 5
        CONTINUE;  -- Skip to next iteration

    IF @counter = 8
        BREAK;  -- Exit loop
END;

T-SQL Error Handling Quick Reference

TRY...CATCH Basics

BEGIN TRY
    -- Code that might fail
    SELECT 1/0;  -- Causes divide by zero
END TRY
BEGIN CATCH
    -- Handle error
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_LINE() AS ErrorLine,
        ERROR_PROCEDURE() AS ErrorProcedure;
END CATCH;

Error Functions

THROW vs RAISERROR

THROW (SQL Server 2012+, Preferred)

-- Throw new error
THROW 50001, 'Custom error message', 1;

-- Re-throw in CATCH block
BEGIN CATCH
    -- Log error first
    INSERT INTO error_log (message) VALUES (ERROR_MESSAGE());

    -- Re-throw original error
    THROW;
END CATCH;

-- THROW with formatted message
DECLARE @msg NVARCHAR(2048) = CONCAT('Order ', @OrderId, ' not found');
THROW 50001, @msg, 1;

RAISERROR (Legacy)

-- Basic usage
RAISERROR('Error occurred', 16, 1);

-- With parameters (printf-style)
RAISERROR('Error: %s, ID: %d', 16, 1, @ErrorMsg, @Id);

-- Without waiting (NOW)
RAISERROR('Warning message', 10, 1) WITH NOWAIT;

-- Using message from sys.messages
EXEC sp_addmessage 50001, 16, 'Order %d not found';
RAISERROR(50001, 16, 1, @OrderId);

Differences

Severity Levels

Error Handling with Transactions

CREATE PROCEDURE usp_ProcessOrder
    @OrderId INT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- Auto-rollback on error

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Process order
        UPDATE orders SET status = 'PROCESSING' WHERE order_id = @OrderId;

        -- Update inventory
        UPDATE inventory SET quantity = quantity - 1
        WHERE product_id IN (SELECT product_id FROM order_items WHERE order_id = @OrderId);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Rollback if in transaction
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- Re-throw error
        THROW;
    END CATCH;
END;
GO

XACT_ABORT

-- When ON: Any error automatically rolls back transaction
SET XACT_ABORT ON;

BEGIN TRANSACTION;
    INSERT INTO table1 VALUES (1);
    INSERT INTO table1 VALUES (1);  -- Duplicate - rolls back everything
COMMIT;  -- Never reached

-- Check state
SELECT XACT_STATE();  -- -1: uncommittable, 0: no transaction, 1: active

XACT_STATE()

BEGIN TRY
    BEGIN TRANSACTION;
    -- Operations...
    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() = -1
    BEGIN
        -- Transaction is uncommittable, must rollback
        ROLLBACK;
    END
    ELSE IF XACT_STATE() = 1
    BEGIN
        -- Transaction is committable (partial work possible)
        -- Usually still want to rollback
        ROLLBACK;
    END

    THROW;
END CATCH;

Nested TRY...CATCH

BEGIN TRY
    BEGIN TRY
        -- Inner operation
        SELECT 1/0;
    END TRY
    BEGIN CATCH
        -- Handle or re-throw
        THROW;
    END CATCH
END TRY
BEGIN CATCH
    -- Outer handler
    PRINT 'Outer catch: ' + ERROR_MESSAGE();
END CATCH;

Error Logging Table

CREATE TABLE dbo.ErrorLog (
    ErrorId INT IDENTITY(1,1) PRIMARY KEY,
    ErrorNumber INT,
    ErrorSeverity INT,
    ErrorState INT,
    ErrorProcedure NVARCHAR(200),
    ErrorLine INT,
    ErrorMessage NVARCHAR(4000),
    UserName NVARCHAR(128) DEFAULT SYSTEM_USER,
    ErrorDateTime DATETIME2 DEFAULT SYSDATETIME()
);

-- Logging procedure
CREATE PROCEDURE dbo.usp_LogError
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.ErrorLog (
        ErrorNumber, ErrorSeverity, ErrorState,
        ErrorProcedure, ErrorLine, ErrorMessage
    )
    VALUES (
        ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(),
        ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE()
    );
END;
GO

-- Usage in CATCH
BEGIN CATCH
    EXEC dbo.usp_LogError;
    THROW;
END CATCH;

Retry Logic

CREATE PROCEDURE usp_RetryOperation
    @MaxRetries INT = 3
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @RetryCount INT = 0;
    DECLARE @Success BIT = 0;

    WHILE @RetryCount < @MaxRetries AND @Success = 0
    BEGIN
        BEGIN TRY
            -- Attempt operation
            BEGIN TRANSACTION;

            UPDATE accounts SET balance = balance - 100 WHERE id = 1;
            UPDATE accounts SET balance = balance + 100 WHERE id = 2;

            COMMIT;
            SET @Success = 1;
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
                ROLLBACK;

            SET @RetryCount = @RetryCount + 1;

            -- Check if retryable error (deadlock, lock timeout)
            IF ERROR_NUMBER() IN (1205, 1222) AND @RetryCount < @MaxRetries
            BEGIN
                WAITFOR DELAY '00:00:01';  -- Wait 1 second
                CONTINUE;
            END

            -- Non-retryable or max retries reached
            THROW;
        END CATCH;
    END;
END;
GO

Common Error Numbers

Custom Error Messages

-- Add to sys.messages
EXEC sp_addmessage
    @msgnum = 50001,
    @severity = 16,
    @msgtext = N'Order %d cannot be processed: %s',
    @lang = 'us_english';

-- Use message
RAISERROR(50001, 16, 1, @OrderId, @Reason);

-- View messages
SELECT * FROM sys.messages WHERE message_id >= 50000;

-- Remove message
EXEC sp_dropmessage @msgnum = 50001;

Best Practices

1. Always use TRY...CATCH in stored procedures 2. Use SET XACT_ABORT ON for transaction safety 3. Check @@TRANCOUNT before ROLLBACK 4. Log errors before re-throwing 5. Use THROW over RAISERROR (SQL 2012+) 6. Use THROW; without parameters to re-throw 7. Consider retry logic for deadlocks 8. Return meaningful error codes from procedures

T-SQL Functions Quick Reference

Function Types

Scalar Function

CREATE OR ALTER FUNCTION dbo.fn_FormatName(
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50)
)
RETURNS NVARCHAR(101)
AS
BEGIN
    RETURN CONCAT(@FirstName, ' ', @LastName);
END;
GO

-- Usage
SELECT dbo.fn_FormatName(first_name, last_name) AS full_name
FROM employees;

-- In WHERE clause
SELECT * FROM employees
WHERE dbo.fn_CalculateAge(birth_date) >= 18;

Inline Table-Valued Function

CREATE OR ALTER FUNCTION dbo.fn_GetEmployeesByDept(
    @DeptId INT
)
RETURNS TABLE
AS
RETURN (
    SELECT
        employee_id,
        first_name,
        last_name,
        salary,
        hire_date
    FROM employees
    WHERE department_id = @DeptId
);
GO

-- Usage (behaves like a table)
SELECT * FROM dbo.fn_GetEmployeesByDept(10);

-- Join with other tables
SELECT e.*, d.department_name
FROM dbo.fn_GetEmployeesByDept(10) e
INNER JOIN departments d ON e.department_id = d.department_id;

-- With CROSS APPLY
SELECT d.department_name, e.*
FROM departments d
CROSS APPLY dbo.fn_GetEmployeesByDept(d.department_id) e;

Multi-Statement Table-Valued Function

CREATE OR ALTER FUNCTION dbo.fn_GetEmployeeHierarchy(
    @ManagerId INT
)
RETURNS @Result TABLE (
    employee_id INT,
    full_name NVARCHAR(200),
    manager_id INT,
    level INT
)
AS
BEGIN
    ;WITH hierarchy AS (
        SELECT
            employee_id,
            first_name + ' ' + last_name AS full_name,
            manager_id,
            0 AS level
        FROM employees
        WHERE employee_id = @ManagerId

        UNION ALL

        SELECT
            e.employee_id,
            e.first_name + ' ' + e.last_name,
            e.manager_id,
            h.level + 1
        FROM employees e
        INNER JOIN hierarchy h ON e.manager_id = h.employee_id
    )
    INSERT INTO @Result
    SELECT * FROM hierarchy;

    RETURN;
END;
GO

-- Usage
SELECT * FROM dbo.fn_GetEmployeeHierarchy(1);

Function Options

SCHEMABINDING

-- Prevents changes to underlying objects
CREATE FUNCTION dbo.fn_GetCount()
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @count INT;
    SELECT @count = COUNT(*) FROM dbo.employees;  -- Must use schema prefix
    RETURN @count;
END;
GO

RETURNS NULL ON NULL INPUT

-- Automatically returns NULL if any input is NULL
CREATE FUNCTION dbo.fn_Multiply(
    @a INT,
    @b INT
)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    RETURN @a * @b;  -- Never executes if either is NULL
END;
GO

EXECUTE AS

CREATE FUNCTION dbo.fn_GetSensitiveData(
    @UserId INT
)
RETURNS NVARCHAR(MAX)
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @data NVARCHAR(MAX);
    SELECT @data = sensitive_info FROM users WHERE user_id = @UserId;
    RETURN @data;
END;
GO

Deterministic vs Non-Deterministic

-- Deterministic: Same input = same output
CREATE FUNCTION dbo.fn_AddTax(@Amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Amount * 1.21;
END;
GO

-- Non-deterministic: Can return different results
-- (Cannot be used in indexed views, computed columns with indexes)
CREATE FUNCTION dbo.fn_GetCurrentUser()
RETURNS NVARCHAR(128)
AS
BEGIN
    RETURN SYSTEM_USER;  -- Non-deterministic
END;
GO

Common Patterns

Date Calculation

CREATE FUNCTION dbo.fn_GetAge(
    @BirthDate DATE
)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE()) -
        CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @BirthDate, GETDATE()), @BirthDate) > GETDATE()
             THEN 1 ELSE 0 END;
END;
GO

String Formatting

CREATE FUNCTION dbo.fn_FormatCurrency(
    @Amount DECIMAL(18,2),
    @CurrencySymbol NVARCHAR(3) = '$'
)
RETURNS NVARCHAR(50)
AS
BEGIN
    RETURN @CurrencySymbol + FORMAT(@Amount, 'N2');
END;
GO

Validation

CREATE FUNCTION dbo.fn_IsValidEmail(
    @Email NVARCHAR(255)
)
RETURNS BIT
AS
BEGIN
    IF @Email LIKE '%_@_%.__%'
       AND @Email NOT LIKE '%[^a-zA-Z0-9.@_-]%'
        RETURN 1;
    RETURN 0;
END;
GO

Split String (Pre-2016)

CREATE FUNCTION dbo.fn_SplitString(
    @String NVARCHAR(MAX),
    @Delimiter NVARCHAR(10)
)
RETURNS @Result TABLE (value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @start INT = 1, @end INT;

    WHILE @start <= LEN(@String) + 1
    BEGIN
        SET @end = CHARINDEX(@Delimiter, @String + @Delimiter, @start);
        INSERT INTO @Result VALUES (SUBSTRING(@String, @start, @end - @start));
        SET @start = @end + LEN(@Delimiter);
    END;

    RETURN;
END;
GO

-- SQL Server 2016+ use STRING_SPLIT instead
SELECT value FROM STRING_SPLIT('a,b,c', ',');

Lookup Function

CREATE FUNCTION dbo.fn_GetDepartmentName(
    @DeptId INT
)
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @name NVARCHAR(100);
    SELECT @name = department_name FROM departments WHERE department_id = @DeptId;
    RETURN ISNULL(@name, 'Unknown');
END;
GO

iTVF vs mTVF Performance

-- iTVF (better performance - optimizer can inline)
CREATE FUNCTION dbo.fn_GetOrders_Inline(@CustomerId INT)
RETURNS TABLE AS RETURN (
    SELECT order_id, total, order_date
    FROM orders WHERE customer_id = @CustomerId
);

-- mTVF (worse performance - optimizer can't see inside)
CREATE FUNCTION dbo.fn_GetOrders_Multi(@CustomerId INT)
RETURNS @Result TABLE (order_id INT, total DECIMAL, order_date DATE)
AS BEGIN
    INSERT INTO @Result
    SELECT order_id, total, order_date
    FROM orders WHERE customer_id = @CustomerId;
    RETURN;
END;

-- Prefer iTVF when possible!

Function Restrictions

Functions CANNOT:

• Modify database state (INSERT, UPDATE, DELETE on permanent tables)
• Use PRINT, RAISERROR
• Call stored procedures
• Use dynamic SQL
• Create/alter database objects
• Use transactions

Metadata

-- View function definition
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.fn_MyFunction'));

-- View all functions
SELECT name, type_desc, create_date
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF')  -- Scalar, Inline TVF, Multi-statement TVF
AND schema_id = SCHEMA_ID('dbo');

-- Check if deterministic
SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.fn_MyFunction'), 'IsDeterministic');

Drop Function

DROP FUNCTION IF EXISTS dbo.fn_MyFunction;

T-SQL Procedures Quick Reference

Basic Syntax

CREATE [OR ALTER] PROCEDURE [schema.]procedure_name
    @param1 datatype [= default] [OUTPUT | OUT],
    @param2 datatype [= default] [OUTPUT | OUT]
AS
BEGIN
    SET NOCOUNT ON;
    -- Procedure body
END;
GO

Parameter Types

CREATE PROCEDURE usp_Example
    @InputParam INT,                    -- Input (default)
    @OptionalParam INT = 10,            -- With default value
    @OutputParam INT OUTPUT,            -- Output parameter
    @InOutParam INT OUTPUT              -- Can be both input and output
AS
BEGIN
    SET NOCOUNT ON;

    SET @OutputParam = @InputParam * 2;
    SET @InOutParam = @InOutParam + 1;
END;
GO

-- Calling
DECLARE @out INT, @inout INT = 5;
EXEC usp_Example
    @InputParam = 10,
    @OutputParam = @out OUTPUT,
    @InOutParam = @inout OUTPUT;

SELECT @out AS OutputValue, @inout AS InOutValue;

Table-Valued Parameters

-- Create type first
CREATE TYPE dbo.EmployeeTableType AS TABLE (
    EmployeeId INT,
    Name NVARCHAR(100),
    Salary DECIMAL(10,2)
);
GO

-- Procedure using TVP
CREATE PROCEDURE usp_InsertEmployees
    @Employees dbo.EmployeeTableType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO employees (employee_id, name, salary)
    SELECT EmployeeId, Name, Salary FROM @Employees;
END;
GO

-- Calling with TVP
DECLARE @emps dbo.EmployeeTableType;
INSERT INTO @emps VALUES (1, 'John', 50000), (2, 'Jane', 60000);
EXEC usp_InsertEmployees @Employees = @emps;

Return Values

CREATE PROCEDURE usp_ProcessOrder
    @OrderId INT
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM orders WHERE order_id = @OrderId)
        RETURN -1;  -- Not found

    IF EXISTS (SELECT 1 FROM orders WHERE order_id = @OrderId AND status = 'PROCESSED')
        RETURN -2;  -- Already processed

    UPDATE orders SET status = 'PROCESSED' WHERE order_id = @OrderId;
    RETURN 0;  -- Success
END;
GO

-- Check return value
DECLARE @result INT;
EXEC @result = usp_ProcessOrder @OrderId = 100;

IF @result = 0 PRINT 'Success';
ELSE IF @result = -1 PRINT 'Order not found';
ELSE IF @result = -2 PRINT 'Already processed';

Temporary Procedures

-- Local temp procedure (current session only)
CREATE PROCEDURE #usp_TempProc
AS
BEGIN
    SELECT 'Temporary procedure';
END;
GO

-- Global temp procedure (all sessions)
CREATE PROCEDURE ##usp_GlobalTempProc
AS
BEGIN
    SELECT 'Global temporary procedure';
END;
GO

Procedure with Transactions

CREATE PROCEDURE usp_TransferFunds
    @FromAccount INT,
    @ToAccount INT,
    @Amount DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- Auto-rollback on error

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Deduct from source
        UPDATE accounts SET balance = balance - @Amount
        WHERE account_id = @FromAccount;

        IF @@ROWCOUNT = 0
            THROW 50001, 'Source account not found', 1;

        -- Add to destination
        UPDATE accounts SET balance = balance + @Amount
        WHERE account_id = @ToAccount;

        IF @@ROWCOUNT = 0
            THROW 50002, 'Destination account not found', 1;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        THROW;  -- Re-throw error
    END CATCH;
END;
GO

Procedure with Result Sets

CREATE PROCEDURE usp_GetDashboardData
    @UserId INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Result set 1: User info
    SELECT user_id, username, email FROM users WHERE user_id = @UserId;

    -- Result set 2: Recent orders
    SELECT TOP 10 order_id, total, order_date
    FROM orders WHERE user_id = @UserId ORDER BY order_date DESC;

    -- Result set 3: Notifications
    SELECT notification_id, message, created_at
    FROM notifications WHERE user_id = @UserId AND is_read = 0;
END;
GO

WITH RECOMPILE

-- Recompile every execution (for varying parameters)
CREATE PROCEDURE usp_SearchProducts
    @Category NVARCHAR(50) = NULL,
    @MinPrice DECIMAL(10,2) = NULL,
    @MaxPrice DECIMAL(10,2) = NULL
WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT product_id, name, price
    FROM products
    WHERE (@Category IS NULL OR category = @Category)
    AND (@MinPrice IS NULL OR price >= @MinPrice)
    AND (@MaxPrice IS NULL OR price <= @MaxPrice);
END;
GO

-- Or recompile specific execution
EXEC usp_SearchProducts @Category = 'Electronics' WITH RECOMPILE;

EXECUTE AS

-- Execute with different security context
CREATE PROCEDURE usp_AdminTask
WITH EXECUTE AS OWNER  -- or 'dbo', 'SELF', 'CALLER', 'user_name'
AS
BEGIN
    SET NOCOUNT ON;
    -- Runs with owner's permissions
    DELETE FROM audit_log WHERE log_date < DATEADD(DAY, -90, GETDATE());
END;
GO

Common Patterns

Batch Processing

CREATE PROCEDURE usp_BatchProcess
    @BatchSize INT = 1000
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @RowsAffected INT = 1;

    WHILE @RowsAffected > 0
    BEGIN
        UPDATE TOP (@BatchSize) orders
        SET processed = 1
        WHERE processed = 0;

        SET @RowsAffected = @@ROWCOUNT;

        -- Optional: Add delay to reduce load
        IF @RowsAffected > 0
            WAITFOR DELAY '00:00:01';
    END;
END;
GO

Pagination

CREATE PROCEDURE usp_GetOrdersPaged
    @PageNumber INT = 1,
    @PageSize INT = 20,
    @TotalCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Get total count
    SELECT @TotalCount = COUNT(*) FROM orders;

    -- Get page
    SELECT order_id, customer_id, total, order_date
    FROM orders
    ORDER BY order_date DESC
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;
GO

Upsert Pattern

CREATE PROCEDURE usp_UpsertCustomer
    @CustomerId INT,
    @Name NVARCHAR(100),
    @Email NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;

    MERGE INTO customers AS target
    USING (SELECT @CustomerId, @Name, @Email) AS source (id, name, email)
    ON target.customer_id = source.id
    WHEN MATCHED THEN
        UPDATE SET name = source.name, email = source.email, updated_at = GETDATE()
    WHEN NOT MATCHED THEN
        INSERT (customer_id, name, email, created_at)
        VALUES (source.id, source.name, source.email, GETDATE());
END;
GO

Error Logging

CREATE PROCEDURE usp_LogError
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO error_log (
        error_number,
        error_message,
        error_severity,
        error_state,
        error_line,
        error_procedure,
        logged_at
    )
    VALUES (
        ERROR_NUMBER(),
        ERROR_MESSAGE(),
        ERROR_SEVERITY(),
        ERROR_STATE(),
        ERROR_LINE(),
        ERROR_PROCEDURE(),
        GETDATE()
    );
END;
GO

-- Usage
BEGIN CATCH
    EXEC usp_LogError;
    THROW;
END CATCH;

Metadata

-- View procedure definition
EXEC sp_helptext 'usp_MyProcedure';

-- Or
SELECT OBJECT_DEFINITION(OBJECT_ID('usp_MyProcedure'));

-- View parameters
SELECT * FROM sys.parameters
WHERE object_id = OBJECT_ID('usp_MyProcedure');

-- View all procedures
SELECT name, create_date, modify_date
FROM sys.procedures
WHERE schema_id = SCHEMA_ID('dbo');

Drop Procedure

DROP PROCEDURE IF EXISTS usp_MyProcedure;
-- Pre-2016:
IF OBJECT_ID('usp_MyProcedure', 'P') IS NOT NULL
    DROP PROCEDURE usp_MyProcedure;