T-SQL Programmability

This section covers Transact-SQL (T-SQL) features that enable you to create and manage programmable objects within SQL Server.

On This Page

Stored Procedures

Stored procedures are precompiled collections of one or more T-SQL statements that are stored on the database server. They offer benefits such as improved performance, enhanced security, and code reusability.

Example: Creating a simple stored procedure


CREATE PROCEDURE usp_GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SELECT
        o.OrderID,
        o.OrderDate,
        od.ProductID,
        od.Quantity
    FROM
        Orders AS o
    JOIN
        OrderDetails AS od ON o.OrderID = od.OrderID
    WHERE
        o.CustomerID = @CustomerID;
END;

To execute this stored procedure, you would use:


EXEC usp_GetCustomerOrders @CustomerID = 101;

Learn more about stored procedures.

User-Defined Functions (UDFs)

User-defined functions allow you to encapsulate logic that can be reused throughout your applications. They can accept input parameters and return a single value or a table.

Example: Creating a scalar UDF


CREATE FUNCTION dbo.CalculateDiscountedPrice (
    @Price DECIMAL(10, 2),
    @DiscountRate DECIMAL(4, 2)
)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    RETURN @Price * (1.0 - @DiscountRate);
END;

Usage:


SELECT dbo.CalculateDiscountedPrice(100.00, 0.10); -- Returns 90.00

Explore user-defined functions.

Triggers

Triggers are special stored procedures that automatically execute when a specific event occurs on a table or view, such as an INSERT, UPDATE, or DELETE operation.

Example: AFTER INSERT Trigger


CREATE TRIGGER trg_AuditProductInsert
ON Products
AFTER INSERT
AS
BEGIN
    INSERT INTO ProductAuditLog (ProductID, Action, Timestamp)
    SELECT
        i.ProductID,
        'INSERT',
        GETDATE()
    FROM
        inserted AS i;
END;

Discover more about triggers.

Views

Views are virtual tables based on the result-set of a T-SQL statement. They simplify complex queries and can be used to restrict data access.

Example: Creating a view


CREATE VIEW vw_CustomerOrderSummary AS
SELECT
    c.CustomerID,
    c.CustomerName,
    COUNT(o.OrderID) AS TotalOrders
FROM
    Customers AS c
LEFT JOIN
    Orders AS o ON c.CustomerID = o.CustomerID
GROUP BY
    c.CustomerID,
    c.CustomerName;

Querying the view:


SELECT * FROM vw_CustomerOrderSummary WHERE TotalOrders > 5;

Read about views.

Common Table Expressions (CTEs)

CTEs are temporary, named result sets that you can reference within a single SELECT, INSERT, UPDATE, or DELETE statement. They are particularly useful for recursive queries and breaking down complex logic.

Example: CTE for hierarchical data


WITH EmployeeHierarchy AS (
    SELECT
        EmployeeID,
        EmployeeName,
        ManagerID,
        0 AS Level
    FROM
        Employees
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        eh.Level + 1
    FROM
        Employees AS e
    INNER JOIN
        EmployeeHierarchy AS eh ON e.ManagerID = eh.EmployeeID
)
SELECT
    EmployeeName,
    Level
FROM
    EmployeeHierarchy
ORDER BY
    Level, EmployeeName;

Understand Common Table Expressions.

Cursors

Cursors allow row-by-row processing of a result set. While powerful, they can be less efficient than set-based operations and should be used judiciously.

Example: Simple cursor usage


DECLARE @ProductName NVARCHAR(100);
DECLARE ProductCursor CURSOR FOR
SELECT ProductName FROM Products WHERE CategoryID = 5;

OPEN ProductCursor;
FETCH NEXT FROM ProductCursor INTO @ProductName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @ProductName;
    FETCH NEXT FROM ProductCursor INTO @ProductName;
END;

CLOSE ProductCursor;
DEALLOCATE ProductCursor;

Details on cursors.

This section provides a foundational understanding of T-SQL programmability. Refer to individual topics for in-depth syntax, examples, and best practices.