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