T-SQL Programming Essentials: Procedures, Functions, and Triggers

T-SQL Programming Essentials: Procedures, Functions, and Triggers

Introduction

T-SQL programming enables building robust database logic with stored procedures, functions, and triggers. This guide covers creating parameterized procedures with error handling, scalar and table-valued functions, DML and DDL triggers, transaction management, and best practices for maintainable database code.

Stored Procedures

Basic Stored Procedure

CREATE PROCEDURE dbo.GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount,
        Status
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
    ORDER BY OrderDate DESC;
END;
GO

-- Execute procedure
EXEC dbo.GetCustomerOrders @CustomerID = 1001;

Parameters and Return Values

CREATE PROCEDURE dbo.CreateOrder
    @CustomerID INT,
    @OrderDate DATE,
    @TotalAmount DECIMAL(10,2),
    @OrderID INT OUTPUT  -- Output parameter
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Insert order
    INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount, Status)
    VALUES (@CustomerID, @OrderDate, @TotalAmount, 'Pending');
    
    -- Return new OrderID
    SET @OrderID = SCOPE_IDENTITY();
    
    -- Return status code
    RETURN 0;  -- Success
END;
GO

-- Execute with output parameter
DECLARE @NewOrderID INT;
DECLARE @ReturnCode INT;

EXEC @ReturnCode = dbo.CreateOrder
    @CustomerID = 1001,
    @OrderDate = '2025-02-10',
    @TotalAmount = 499.99,
    @OrderID = @NewOrderID OUTPUT;

SELECT @NewOrderID AS OrderID, @ReturnCode AS Status;

Error Handling with TRY-CATCH

CREATE PROCEDURE dbo.TransferFunds
    @FromAccountID INT,
    @ToAccountID INT,
    @Amount DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Validate accounts exist
        IF NOT EXISTS (SELECT 1 FROM dbo.Accounts WHERE AccountID = @FromAccountID)
        BEGIN
            THROW 50001, 'Source account not found', 1;
        END;
        
        IF NOT EXISTS (SELECT 1 FROM dbo.Accounts WHERE AccountID = @ToAccountID)
        BEGIN
            THROW 50002, 'Destination account not found', 1;
        END;
        
        -- Check sufficient balance
        DECLARE @CurrentBalance DECIMAL(10,2);
        SELECT @CurrentBalance = Balance 
        FROM dbo.Accounts 
        WHERE AccountID = @FromAccountID;
        
        IF @CurrentBalance < @Amount
        BEGIN
            THROW 50003, 'Insufficient funds', 1;
        END;
        
        -- Perform transfer
        UPDATE dbo.Accounts
        SET Balance = Balance - @Amount
        WHERE AccountID = @FromAccountID;
        
        UPDATE dbo.Accounts
        SET Balance = Balance + @Amount
        WHERE AccountID = @ToAccountID;
        
        -- Log transaction
        INSERT INTO dbo.TransactionLog (FromAccountID, ToAccountID, Amount, TransactionDate)
        VALUES (@FromAccountID, @ToAccountID, @Amount, GETUTCDATE());
        
        COMMIT TRANSACTION;
        
        RETURN 0;  -- Success
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- Log error
        INSERT INTO dbo.ErrorLog (ErrorNumber, ErrorMessage, ErrorProcedure, ErrorLine, ErrorTime)
        VALUES (
            ERROR_NUMBER(),
            ERROR_MESSAGE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            GETUTCDATE()
        );
        
        -- Re-throw error
        THROW;
    END CATCH;
END;
GO

Optional Parameters with Defaults

CREATE PROCEDURE dbo.SearchProducts
    @CategoryID INT = NULL,
    @MinPrice DECIMAL(10,2) = NULL,
    @MaxPrice DECIMAL(10,2) = NULL,
    @SearchTerm NVARCHAR(100) = NULL,
    @SortColumn NVARCHAR(50) = 'ProductName',
    @SortDirection NVARCHAR(4) = 'ASC'
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        ProductID,
        ProductName,
        CategoryID,
        Price,
        StockQuantity
    FROM dbo.Products
    WHERE 
        (@CategoryID IS NULL OR CategoryID = @CategoryID)
        AND (@MinPrice IS NULL OR Price >= @MinPrice)
        AND (@MaxPrice IS NULL OR Price <= @MaxPrice)
        AND (@SearchTerm IS NULL OR ProductName LIKE '%' + @SearchTerm + '%')
    ORDER BY 
        CASE 
            WHEN @SortColumn = 'ProductName' AND @SortDirection = 'ASC' THEN ProductName
        END ASC,
        CASE 
            WHEN @SortColumn = 'ProductName' AND @SortDirection = 'DESC' THEN ProductName
        END DESC,
        CASE 
            WHEN @SortColumn = 'Price' AND @SortDirection = 'ASC' THEN Price
        END ASC,
        CASE 
            WHEN @SortColumn = 'Price' AND @SortDirection = 'DESC' THEN Price
        END DESC;
END;
GO

-- Execute with various parameter combinations
EXEC dbo.SearchProducts;  -- All parameters use defaults
EXEC dbo.SearchProducts @CategoryID = 5;
EXEC dbo.SearchProducts @MinPrice = 50, @MaxPrice = 200;
EXEC dbo.SearchProducts @SearchTerm = 'laptop', @SortColumn = 'Price', @SortDirection = 'DESC';

Functions

Scalar Functions

-- Calculate age from birth date
CREATE FUNCTION dbo.fn_CalculateAge(@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

-- Use in query
SELECT 
    CustomerID,
    FirstName,
    LastName,
    BirthDate,
    dbo.fn_CalculateAge(BirthDate) AS Age
FROM dbo.Customers;

-- Calculate tax
CREATE FUNCTION dbo.fn_CalculateTax(@Amount DECIMAL(10,2), @TaxRate DECIMAL(5,4))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN ROUND(@Amount * @TaxRate, 2);
END;
GO

SELECT 
    OrderID,
    TotalAmount,
    dbo.fn_CalculateTax(TotalAmount, 0.0825) AS TaxAmount,
    TotalAmount + dbo.fn_CalculateTax(TotalAmount, 0.0825) AS GrandTotal
FROM dbo.Orders;

Inline Table-Valued Functions (Recommended)

-- Get customer orders with filtering
CREATE FUNCTION dbo.fn_GetCustomerOrders
(
    @CustomerID INT,
    @StartDate DATE = NULL,
    @EndDate DATE = NULL
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        o.OrderID,
        o.OrderDate,
        o.TotalAmount,
        o.Status,
        COUNT(od.OrderDetailID) AS ItemCount
    FROM dbo.Orders o
    INNER JOIN dbo.OrderDetails od ON o.OrderID = od.OrderID
    WHERE 
        o.CustomerID = @CustomerID
        AND (@StartDate IS NULL OR o.OrderDate >= @StartDate)
        AND (@EndDate IS NULL OR o.OrderDate <= @EndDate)
    GROUP BY o.OrderID, o.OrderDate, o.TotalAmount, o.Status
);
GO

-- Use like a table
SELECT * FROM dbo.fn_GetCustomerOrders(1001, '2025-01-01', '2025-12-31');

-- Join with other tables
SELECT 
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    o.TotalAmount
FROM dbo.Customers c
CROSS APPLY dbo.fn_GetCustomerOrders(c.CustomerID, '2025-01-01', '2025-12-31') o
ORDER BY c.CustomerName, o.OrderDate;

Multi-Statement Table-Valued Functions

-- Build organizational hierarchy
CREATE FUNCTION dbo.fn_GetEmployeeHierarchy(@ManagerID INT)
RETURNS @Hierarchy TABLE
(
    EmployeeID INT,
    FullName NVARCHAR(100),
    Level INT,
    ManagerPath NVARCHAR(500)
)
AS
BEGIN
    -- Insert root level
    INSERT INTO @Hierarchy (EmployeeID, FullName, Level, ManagerPath)
    SELECT 
        EmployeeID,
        FirstName + ' ' + LastName,
        0,
        CAST(EmployeeID AS NVARCHAR(500))
    FROM dbo.Employees
    WHERE EmployeeID = @ManagerID;
    
    -- Recursive CTE for hierarchy
    WITH EmployeeCTE AS
    (
        SELECT 
            e.EmployeeID,
            e.FirstName + ' ' + e.LastName AS FullName,
            1 AS Level,
            CAST(e.ManagerID AS NVARCHAR(500)) + '/' + CAST(e.EmployeeID AS NVARCHAR(500)) AS ManagerPath
        FROM dbo.Employees e
        WHERE e.ManagerID = @ManagerID
        
        UNION ALL
        
        SELECT 
            e.EmployeeID,
            e.FirstName + ' ' + e.LastName,
            cte.Level + 1,
            cte.ManagerPath + '/' + CAST(e.EmployeeID AS NVARCHAR(500))
        FROM dbo.Employees e
        INNER JOIN EmployeeCTE cte ON e.ManagerID = cte.EmployeeID
    )
    INSERT INTO @Hierarchy (EmployeeID, FullName, Level, ManagerPath)
    SELECT EmployeeID, FullName, Level, ManagerPath
    FROM EmployeeCTE;
    
    RETURN;
END;
GO

-- Use function
SELECT 
    REPLICATE('  ', Level) + FullName AS OrgChart,
    Level,
    ManagerPath
FROM dbo.fn_GetEmployeeHierarchy(1)
ORDER BY ManagerPath;

Triggers

AFTER INSERT Trigger

-- Audit new orders
CREATE TRIGGER trg_Orders_AfterInsert
ON dbo.Orders
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Log new orders
    INSERT INTO dbo.OrderAudit (OrderID, Action, ChangedBy, ChangedDate)
    SELECT 
        OrderID,
        'INSERT',
        SUSER_SNAME(),
        GETUTCDATE()
    FROM inserted;
    
    -- Send notification for large orders
    INSERT INTO dbo.Notifications (OrderID, Message, CreatedDate)
    SELECT 
        OrderID,
        'Large order received: $' + CAST(TotalAmount AS VARCHAR(20)),
        GETUTCDATE()
    FROM inserted
    WHERE TotalAmount > 10000;
END;
GO

AFTER UPDATE Trigger

-- Track price changes
CREATE TRIGGER trg_Products_AfterUpdate
ON dbo.Products
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Only log if price changed
    IF UPDATE(Price)
    BEGIN
        INSERT INTO dbo.PriceHistory (ProductID, OldPrice, NewPrice, ChangedBy, ChangedDate)
        SELECT 
            i.ProductID,
            d.Price AS OldPrice,
            i.Price AS NewPrice,
            SUSER_SNAME(),
            GETUTCDATE()
        FROM inserted i
        INNER JOIN deleted d ON i.ProductID = d.ProductID
        WHERE i.Price <> d.Price;
    END;
END;
GO

INSTEAD OF Trigger

-- Enforce business rules on view
CREATE VIEW dbo.vw_CustomerOrders
AS
SELECT 
    c.CustomerID,
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    o.TotalAmount
FROM dbo.Customers c
LEFT JOIN dbo.Orders o ON c.CustomerID = o.CustomerID;
GO

CREATE TRIGGER trg_vw_CustomerOrders_InsteadOfInsert
ON dbo.vw_CustomerOrders
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Insert into Orders table
    INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount, Status)
    SELECT 
        CustomerID,
        OrderDate,
        TotalAmount,
        'Pending'
    FROM inserted
    WHERE CustomerID IS NOT NULL;
    
    -- Validate customer exists
    IF EXISTS (SELECT 1 FROM inserted WHERE CustomerID NOT IN (SELECT CustomerID FROM dbo.Customers))
    BEGIN
        RAISERROR('Invalid CustomerID', 16, 1);
        ROLLBACK TRANSACTION;
    END;
END;
GO

DDL Trigger

-- Prevent table drops in production
CREATE TRIGGER trg_PreventTableDrop
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    PRINT 'Table drops are not allowed in production';
    ROLLBACK;
END;
GO

-- Audit schema changes
CREATE TRIGGER trg_AuditSchemaChanges
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @EventData XML = EVENTDATA();
    
    INSERT INTO dbo.SchemaChangeLog (EventType, ObjectName, DatabaseName, TSQLCommand, ChangedBy, ChangedDate)
    VALUES (
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(256)'),
        @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(256)'),
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        SUSER_SNAME(),
        GETUTCDATE()
    );
END;
GO

Transactions and Locking

Transaction Management

CREATE PROCEDURE dbo.ProcessOrder
    @OrderID INT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Update order status
        UPDATE dbo.Orders
        SET Status = 'Processing',
            ProcessedDate = GETUTCDATE()
        WHERE OrderID = @OrderID;
        
        -- Decrement inventory
        UPDATE p
        SET p.StockQuantity = p.StockQuantity - od.Quantity
        FROM dbo.Products p
        INNER JOIN dbo.OrderDetails od ON p.ProductID = od.ProductID
        WHERE od.OrderID = @OrderID;
        
        -- Check for negative inventory
        IF EXISTS (
            SELECT 1 FROM dbo.Products p
            INNER JOIN dbo.OrderDetails od ON p.ProductID = od.ProductID
            WHERE od.OrderID = @OrderID AND p.StockQuantity < 0
        )
        BEGIN
            THROW 50004, 'Insufficient inventory', 1;
        END;
        
        -- Create shipment record
        INSERT INTO dbo.Shipments (OrderID, ShipDate, Status)
        VALUES (@OrderID, GETUTCDATE(), 'Pending');
        
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;
GO

Isolation Levels

-- Read Committed (default)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM dbo.Products WHERE ProductID = 1;
COMMIT;

-- Read Uncommitted (dirty reads)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM dbo.Products;  -- Can read uncommitted changes

-- Repeatable Read (prevents non-repeatable reads)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM dbo.Products WHERE ProductID = 1;
-- Row locked until commit
COMMIT;

-- Serializable (prevents phantom reads)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM dbo.Products WHERE CategoryID = 5;
-- Range locked, no new rows can be inserted
COMMIT;

-- Snapshot (row versioning)
ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT * FROM dbo.Products;  -- Reads snapshot from start of transaction
COMMIT;

Handling Deadlocks

CREATE PROCEDURE dbo.UpdateInventorySafe
    @ProductID INT,
    @Quantity INT
AS
BEGIN
    DECLARE @Retries INT = 3;
    DECLARE @RetryCount INT = 0;
    
    WHILE @RetryCount < @Retries
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
            
            -- Update with UPDLOCK hint to reduce deadlocks
            UPDATE dbo.Products WITH (UPDLOCK, ROWLOCK)
            SET StockQuantity = StockQuantity - @Quantity
            WHERE ProductID = @ProductID;
            
            COMMIT TRANSACTION;
            RETURN 0;  -- Success
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
                ROLLBACK TRANSACTION;
            
            -- Check if deadlock victim
            IF ERROR_NUMBER() = 1205
            BEGIN
                SET @RetryCount = @RetryCount + 1;
                WAITFOR DELAY '00:00:00.100';  -- Wait 100ms before retry
                
                IF @RetryCount >= @Retries
                    THROW;  -- Give up after retries
            END
            ELSE
            BEGIN
                THROW;  -- Re-throw other errors
            END;
        END CATCH;
    END;
END;
GO

Best Practices

Avoid Cursors - Use Set-Based Operations

-- ❌ BAD - Cursor-based approach
DECLARE @ProductID INT;
DECLARE cur CURSOR FOR SELECT ProductID FROM dbo.Products;

OPEN cur;
FETCH NEXT FROM cur INTO @ProductID;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE dbo.Products
    SET Price = Price * 1.1
    WHERE ProductID = @ProductID;
    
    FETCH NEXT FROM cur INTO @ProductID;
END;

CLOSE cur;
DEALLOCATE cur;

-- ✅ GOOD - Set-based approach
UPDATE dbo.Products
SET Price = Price * 1.1;

Use Schema Names

-- ❌ BAD
SELECT * FROM Orders;

-- ✅ GOOD
SELECT * FROM dbo.Orders;

Handle Errors Properly

CREATE PROCEDURE dbo.DeleteCustomer
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Check if customer has orders
        IF EXISTS (SELECT 1 FROM dbo.Orders WHERE CustomerID = @CustomerID)
        BEGIN
            THROW 50005, 'Cannot delete customer with existing orders', 1;
        END;
        
        -- Delete customer
        DELETE FROM dbo.Customers WHERE CustomerID = @CustomerID;
        
        IF @@ROWCOUNT = 0
        BEGIN
            THROW 50006, 'Customer not found', 1;
        END;
        
        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- Log and re-throw
        INSERT INTO dbo.ErrorLog (ErrorMessage, ErrorProcedure, ErrorLine)
        VALUES (ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE());
        
        THROW;
    END CATCH;
END;
GO

Avoid SELECT *

-- ❌ BAD
SELECT * FROM dbo.Customers;

-- ✅ GOOD
SELECT CustomerID, FirstName, LastName, Email
FROM dbo.Customers;

Key Takeaways

• Use stored procedures for complex business logic
• Implement proper error handling with TRY-CATCH
• Prefer inline table-valued functions over multi-statement
• Use AFTER triggers for auditing, INSTEAD OF for views
• Manage transactions explicitly with BEGIN/COMMIT/ROLLBACK
• Choose appropriate isolation levels for concurrency
• Avoid cursors - use set-based operations
• Always use schema names and specific column lists
• Handle deadlocks with retry logic
• Test thoroughly with realistic data volumes

Next Steps

• Review existing procedures for error handling
• Convert cursors to set-based operations
• Implement auditing with triggers
• Add transaction management to multi-step operations
• Document all stored procedures and functions

Additional Resources

• Stored Procedures
• User-Defined Functions
• DML Triggers
• Transactions

---

Write once. Execute everywhere. Maintain forever.