T-SQL Programmability Examples

Explore practical examples demonstrating various T-SQL programmability features. These examples are designed to illustrate common use cases and best practices.

Stored Procedures

Example 1: Creating a Basic Stored Procedure to Retrieve Customer Data

This example shows how to create a simple stored procedure that returns all customers from a specified table.


CREATE PROCEDURE GetCustomers
AS
BEGIN
    SELECT CustomerID, CompanyName, ContactName, City
    FROM Customers;
END;
GO

-- Execute the stored procedure
EXEC GetCustomers;

Example 2: Stored Procedure with Input Parameters

Demonstrates a stored procedure that filters customer data based on a provided city name.


CREATE PROCEDURE GetCustomersByCity
    @CityName NVARCHAR(50)
AS
BEGIN
    SELECT CustomerID, CompanyName, ContactName, City
    FROM Customers
    WHERE City = @CityName;
END;
GO

-- Execute the stored procedure with a parameter
EXEC GetCustomersByCity @CityName = 'London';

User-Defined Functions (UDFs)

Example 1: Scalar UDF for Calculating Order Total

A scalar function that calculates the total price of an order item based on quantity and unit price.


CREATE FUNCTION dbo.CalculateOrderItemTotal (@Quantity INT, @UnitPrice DECIMAL(10, 2))
RETURNS DECIMAL(18, 2)
AS
BEGIN
    RETURN @Quantity * @UnitPrice;
END;
GO

-- Using the scalar function in a query
SELECT OrderID, ProductID, dbo.CalculateOrderItemTotal(Quantity, UnitPrice) AS TotalPrice
FROM [Order Details];

Example 2: Table-Valued UDF to Get Products by Category

A multi-statement table-valued function that returns a list of products belonging to a specific category.


CREATE FUNCTION dbo.GetProductsByCategory (@CategoryID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT ProductID, ProductName, UnitPrice
    FROM Products
    WHERE CategoryID = @CategoryID
);
GO

-- Using the table-valued function
SELECT * FROM dbo.GetProductsByCategory(3);

Triggers

Example 1: Audit Trigger for Inserts on a Table

An AFTER INSERT trigger that logs changes to an 'Employees' table into an audit table.


-- Assume an AuditLog table exists:
-- CREATE TABLE AuditLog (LogID INT IDENTITY(1,1) PRIMARY KEY, TableName VARCHAR(50), ActionType VARCHAR(20), ChangeDate DATETIME);

CREATE TRIGGER TR_Employees_AuditInsert
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO AuditLog (TableName, ActionType, ChangeDate)
    SELECT 'Employees', 'INSERT', GETDATE()
    FROM inserted;
END;
GO

-- Test the trigger by inserting a new employee (assuming EmployeeID is identity)
-- INSERT INTO Employees (FirstName, LastName, HireDate) VALUES ('Jane', 'Doe', GETDATE());

Cursors

Example 1: Iterating Through Orders to Calculate Totals

This example demonstrates using a cursor to loop through each order, calculate its total, and update a summary table. (Note: Cursors should generally be avoided for performance reasons when set-based operations can be used.)


DECLARE @OrderID INT;
DECLARE @OrderTotal DECIMAL(18, 2);

DECLARE order_cursor CURSOR FOR
SELECT OrderID
FROM Orders;

OPEN order_cursor;

FETCH NEXT FROM order_cursor INTO @OrderID;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @OrderTotal = (SELECT SUM(Quantity * UnitPrice) FROM [Order Details] WHERE OrderID = @OrderID);

    -- In a real scenario, you might update a summary table or perform other actions
    PRINT 'Order ID: ' + CAST(@OrderID AS VARCHAR) + ', Total: ' + CAST(@OrderTotal AS VARCHAR);

    FETCH NEXT FROM order_cursor INTO @OrderID;
END;

CLOSE order_cursor;
DEALLOCATE order_cursor;