T-SQL Stored Procedures Tutorial

Stored procedures are named collections of T-SQL statements stored in the database. They encapsulate business logic, improve performance through plan caching, and provide a security layer between applications and tables. Stored procedures are one of the most powerful features in SQL Server.

Creating a Basic Stored Procedure

Use CREATE PROCEDURE (or CREATE PROC) to define a stored procedure. Execute it with EXEC.

CREATE PROCEDURE usp_GetActiveEmployees
AS
BEGIN
    SET NOCOUNT ON;

    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees
    WHERE IsActive = 1
    ORDER BY LastName;
END;

-- Execute the procedure
EXEC usp_GetActiveEmployees;

Input Parameters

Parameters make stored procedures flexible and reusable. You can assign default values so callers can omit optional parameters.

CREATE PROCEDURE usp_GetEmployeesByDepartment
    @Department NVARCHAR(50),
    @MinSalary DECIMAL(10,2) = 0  -- default value
AS
BEGIN
    SET NOCOUNT ON;

    SELECT FirstName, LastName, Salary
    FROM Employees
    WHERE Department = @Department
      AND Salary >= @MinSalary
    ORDER BY Salary DESC;
END;

-- Call with both parameters
EXEC usp_GetEmployeesByDepartment
    @Department = 'Engineering',
    @MinSalary = 80000;

-- Call with only required parameter
EXEC usp_GetEmployeesByDepartment @Department = 'Sales';

Output Parameters

Output parameters return values to the caller, which is useful when you need a computed result like a count or a generated ID.

CREATE PROCEDURE usp_CreateOrder
    @CustomerID INT,
    @Total DECIMAL(12,2),
    @NewOrderID BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Orders (CustomerID, OrderDate, OrderTotal)
    VALUES (@CustomerID, GETDATE(), @Total);

    SET @NewOrderID = SCOPE_IDENTITY();
END;

-- Call and capture the output
DECLARE @OrderID BIGINT;
EXEC usp_CreateOrder
    @CustomerID = 42,
    @Total = 299.99,
    @NewOrderID = @OrderID OUTPUT;

SELECT @OrderID AS CreatedOrderID;

Variables and Control Flow

T-SQL supports local variables, IF/ELSE branching, and WHILE loops inside stored procedures.

CREATE PROCEDURE usp_ApplyDiscount
    @ProductID INT,
    @DiscountPercent DECIMAL(5,2)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @CurrentPrice DECIMAL(10,2);

    SELECT @CurrentPrice = Price
    FROM Products WHERE ProductID = @ProductID;

    IF @CurrentPrice IS NULL
    BEGIN
        RAISERROR('Product not found.', 16, 1);
        RETURN;
    END

    UPDATE Products
    SET Price = @CurrentPrice * (1 - @DiscountPercent / 100)
    WHERE ProductID = @ProductID;
END;

Error Handling with TRY...CATCH

Wrap your logic in a TRY...CATCH block to handle errors gracefully. Use ERROR_MESSAGE() and related functions to capture error details.

CREATE PROCEDURE usp_TransferFunds
    @FromAccount INT,
    @ToAccount INT,
    @Amount DECIMAL(12,2)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE Accounts SET Balance = Balance - @Amount
        WHERE AccountID = @FromAccount;

        UPDATE Accounts SET Balance = Balance + @Amount
        WHERE AccountID = @ToAccount;

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

        THROW;  -- re-raise the error
    END CATCH
END;

Stored procedures are a cornerstone of SQL Server development. They provide encapsulation, security through execute permissions, and performance benefits from cached execution plans. Use them to keep business logic close to your data.