Advanced Stored Procedures in SQL Server

You will learn:

• Input parameters with DEFAULT values, optional filters
• OUTPUT parameters and RETURN codes
• Validation + TRY...CATCH + transaction inside a procedure
• Table-Valued Parameters (pass many rows at once)
• Pagination & sorting procedure
• Procedure calling another procedure, INSERT ... EXEC
• WITH RECOMPILE / OPTION(RECOMPILE) and parameter sniffing (concept)

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic tables: AuditLog and a copy of stock for order placement

DROP TABLE IF EXISTS dbo.AuditLog;

CREATE TABLE dbo.AuditLog
(
    AuditID    INT IDENTITY(1,1) PRIMARY KEY,
    ProcName   SYSNAME        NOT NULL,
    Action     VARCHAR(50)    NOT NULL,
    Details    NVARCHAR(4000) NULL,
    LoggedBy   SYSNAME        NOT NULL DEFAULT SUSER_SNAME(),
    LoggedAt   DATETIME2(0)   NOT NULL DEFAULT SYSDATETIME()
);
GO

-- Table type for Table-Valued Parameter (TVP).
-- Drop the procedure that uses it first, otherwise DROP TYPE fails.
DROP PROCEDURE IF EXISTS dbo.usp_PlaceOrder;
DROP TYPE IF EXISTS dbo.OrderLineType;
GO
CREATE TYPE dbo.OrderLineType AS TABLE
(
    ProductID INT NOT NULL PRIMARY KEY,
    Quantity  INT NOT NULL CHECK (Quantity > 0)
);
GO

Proc 1: Default parameters + optional filters (search procedure)

CREATE OR ALTER PROCEDURE dbo.usp_SearchEmployees
    @DepartmentID INT           = NULL,     -- NULL means "any"
    @MinSalary    DECIMAL(10,2) = 0,
    @City         VARCHAR(50)   = NULL,
    @OnlyActive   BIT           = 1
AS
BEGIN
    SET NOCOUNT ON;   -- hides "(n rows affected)" messages, slightly faster

    SELECT e.EmployeeID, e.FirstName, e.LastName, e.JobTitle, e.Salary, e.City, d.DepartmentName
    FROM dbo.Employees e
    LEFT JOIN dbo.Departments d ON d.DepartmentID = e.DepartmentID
    WHERE (@DepartmentID IS NULL OR e.DepartmentID = @DepartmentID)
      AND e.Salary >= @MinSalary
      AND (@City IS NULL OR e.City = @City)
      AND (@OnlyActive = 0 OR e.IsActive = 1)
    ORDER BY e.Salary DESC
    OPTION (RECOMPILE);   -- builds a fresh plan for each parameter combination ("catch-all" query fix)
END;
GO

EXEC dbo.usp_SearchEmployees;                                  -- all defaults
EXEC dbo.usp_SearchEmployees @DepartmentID = 4;                -- IT only
EXEC dbo.usp_SearchEmployees @City = 'Hyderabad', @MinSalary = 60000;
EXEC dbo.usp_SearchEmployees @DepartmentID = 4, @OnlyActive = 0;   -- include inactive
GO

Proc 2: OUTPUT parameters + Return code

CREATE OR ALTER PROCEDURE dbo.usp_GetCustomerStats
    @CustomerID   INT,
    @TotalOrders  INT           OUTPUT,
    @TotalSpent   DECIMAL(14,2) OUTPUT,
    @LastOrderOn  DATE          OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
        RETURN -1;     -- customer not found

    SELECT @TotalOrders = COUNT(DISTINCT o.OrderID),
           @TotalSpent  = ISNULL(SUM(oi.LineTotal), 0),
           @LastOrderOn = MAX(o.OrderDate)
    FROM dbo.Orders o
    LEFT JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
    WHERE o.CustomerID = @CustomerID;

    IF @TotalOrders = 0 RETURN 1;   -- found, but no orders
    RETURN 0;                       -- success
END;
GO

DECLARE @rc INT, @orders INT, @spent DECIMAL(14,2), @last DATE;

EXEC @rc = dbo.usp_GetCustomerStats @CustomerID = 1,
           @TotalOrders = @orders OUTPUT, @TotalSpent = @spent OUTPUT, @LastOrderOn = @last OUTPUT;
SELECT 'Customer 1' AS Test, @rc AS ReturnCode, @orders AS Orders, @spent AS Spent, @last AS LastOrder;

EXEC @rc = dbo.usp_GetCustomerStats 18, @orders OUTPUT, @spent OUTPUT, @last OUTPUT;
SELECT 'Customer 18' AS Test, @rc AS ReturnCode, @orders AS Orders, @spent AS Spent;

EXEC @rc = dbo.usp_GetCustomerStats 999, @orders OUTPUT, @spent OUTPUT, @last OUTPUT;
SELECT 'Customer 999' AS Test, @rc AS ReturnCode;
GO

Proc 3: Table-Valued Parameter + validation + transaction + TRY/CATCH

Place an order with many lines in ONE call

CREATE OR ALTER PROCEDURE dbo.usp_PlaceOrder
    @CustomerID INT,
    @EmployeeID INT,
    @Lines      dbo.OrderLineType READONLY,   -- TVPs must be READONLY
    @NewOrderID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;   -- any runtime error rolls back the whole transaction

    -- 1) Validation (before starting a transaction)
    IF NOT EXISTS (SELECT 1 FROM @Lines)
        THROW 50001, 'Order must have at least one line.', 1;

    IF EXISTS (SELECT 1 FROM @Lines l LEFT JOIN dbo.Products p ON p.ProductID = l.ProductID
               WHERE p.ProductID IS NULL OR p.Discontinued = 1)
        THROW 50002, 'One or more products are invalid or discontinued.', 1;

    IF EXISTS (SELECT 1 FROM @Lines l JOIN dbo.Products p ON p.ProductID = l.ProductID
               WHERE p.StockQty < l.Quantity)
        THROW 50003, 'Not enough stock for one or more products.', 1;

    BEGIN TRY
        BEGIN TRAN;

            INSERT INTO dbo.Orders (CustomerID, EmployeeID, OrderDate, Status, ShipCity)
            SELECT CustomerID, @EmployeeID, CAST(GETDATE() AS DATE), 'Pending', City
            FROM dbo.Customers WHERE CustomerID = @CustomerID;

            SET @NewOrderID = SCOPE_IDENTITY();   -- ID generated in THIS scope

            INSERT INTO dbo.OrderItems (OrderID, ProductID, Quantity, UnitPrice)
            SELECT @NewOrderID, l.ProductID, l.Quantity, p.UnitPrice
            FROM @Lines l JOIN dbo.Products p ON p.ProductID = l.ProductID;

            UPDATE p SET p.StockQty = p.StockQty - l.Quantity
            FROM dbo.Products p JOIN @Lines l ON l.ProductID = p.ProductID;

            INSERT INTO dbo.AuditLog (ProcName, Action, Details)
            VALUES (OBJECT_NAME(@@PROCID), 'ORDER_PLACED',
                    CONCAT('OrderID=', @NewOrderID, ', Lines=', (SELECT COUNT(*) FROM @Lines)));

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        INSERT INTO dbo.AuditLog (ProcName, Action, Details)
        VALUES (OBJECT_NAME(@@PROCID), 'ORDER_FAILED', ERROR_MESSAGE());
        THROW;    -- re-raise the original error to the caller
    END CATCH;
END;
GO

-- Test inside a transaction that we roll back, so core data stays the same
BEGIN TRAN;
    DECLARE @lines dbo.OrderLineType, @OrderID INT;
    INSERT INTO @lines (ProductID, Quantity) VALUES (1, 2), (9, 5), (16, 10);

    EXEC dbo.usp_PlaceOrder @CustomerID = 3, @EmployeeID = 8, @Lines = @lines, @NewOrderID = @OrderID OUTPUT;

    SELECT * FROM dbo.Orders     WHERE OrderID = @OrderID;
    SELECT * FROM dbo.OrderItems WHERE OrderID = @OrderID;
    SELECT * FROM dbo.AuditLog;
ROLLBACK TRAN;
GO

-- Validation error demo (stock check fails)
BEGIN TRY
    DECLARE @lines dbo.OrderLineType, @OrderID INT;
    INSERT INTO @lines (ProductID, Quantity) VALUES (3, 500);   -- only 10 MacBooks in stock
    EXEC dbo.usp_PlaceOrder 3, 8, @lines, @OrderID OUTPUT;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrNo, ERROR_MESSAGE() AS ErrMsg;
END CATCH;
GO

(3 rows affected)

(1 row affected)

Proc 4: Pagination + safe dynamic sorting (whitelist)

CREATE OR ALTER PROCEDURE dbo.usp_GetProductsPaged
    @PageNumber INT         = 1,
    @PageSize   INT         = 5,
    @SortBy     VARCHAR(20) = 'Name',   -- Name | Price | Stock
    @SortDir    VARCHAR(4)  = 'ASC',
    @TotalRows  INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF @PageNumber < 1 SET @PageNumber = 1;
    IF @PageSize NOT BETWEEN 1 AND 100 SET @PageSize = 10;

    SELECT @TotalRows = COUNT(*) FROM dbo.Products;

    SELECT ProductID, ProductName, UnitPrice, StockQty
    FROM dbo.Products
    ORDER BY
        CASE WHEN @SortBy = 'Name'  AND @SortDir = 'ASC'  THEN ProductName END ASC,
        CASE WHEN @SortBy = 'Name'  AND @SortDir = 'DESC' THEN ProductName END DESC,
        CASE WHEN @SortBy = 'Price' AND @SortDir = 'ASC'  THEN UnitPrice   END ASC,
        CASE WHEN @SortBy = 'Price' AND @SortDir = 'DESC' THEN UnitPrice   END DESC,
        CASE WHEN @SortBy = 'Stock' AND @SortDir = 'ASC'  THEN StockQty    END ASC,
        CASE WHEN @SortBy = 'Stock' AND @SortDir = 'DESC' THEN StockQty    END DESC,
        ProductID
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;
GO

DECLARE @total INT;
EXEC dbo.usp_GetProductsPaged @PageNumber = 1, @PageSize = 5, @SortBy = 'Price', @SortDir = 'DESC', @TotalRows = @total OUTPUT;
EXEC dbo.usp_GetProductsPaged @PageNumber = 2, @PageSize = 5, @SortBy = 'Price', @SortDir = 'DESC', @TotalRows = @total OUTPUT;
SELECT @total AS TotalProducts, CEILING(@total / 5.0) AS TotalPages;
GO

Proc 5: Multiple result sets + nested procedure call + INSERT ... EXEC

CREATE OR ALTER PROCEDURE dbo.usp_DepartmentReport
    @DepartmentID INT
AS
BEGIN
    SET NOCOUNT ON;
    -- Result set 1 : header
    SELECT DepartmentName, Location, Budget FROM dbo.Departments WHERE DepartmentID = @DepartmentID;
    -- Result set 2 : employees (re-using another procedure)
    EXEC dbo.usp_SearchEmployees @DepartmentID = @DepartmentID, @OnlyActive = 0;
END;
GO

EXEC dbo.usp_DepartmentReport @DepartmentID = 4;
GO

-- INSERT ... EXEC : capture a procedure's single result set into a table
DROP TABLE IF EXISTS #ITTeam;
CREATE TABLE #ITTeam (EmployeeID INT, FirstName VARCHAR(50), LastName VARCHAR(50), JobTitle VARCHAR(50),
                      Salary DECIMAL(10,2), City VARCHAR(50), DepartmentName VARCHAR(50));

INSERT INTO #ITTeam
EXEC dbo.usp_SearchEmployees @DepartmentID = 4;

SELECT COUNT(*) AS ITEmployees, SUM(Salary) AS ITPayroll FROM #ITTeam;
DROP TABLE #ITTeam;
GO

(6 rows affected)

Useful System Queries

-- List procedures in this database
SELECT name, create_date, modify_date FROM sys.procedures ORDER BY name;

-- See the source code of a procedure
EXEC sp_helptext 'dbo.usp_GetCustomerStats';

-- Parameters of a procedure
SELECT p.name, TYPE_NAME(p.user_type_id) AS DataType, p.is_output, p.has_default_value
FROM sys.parameters p
WHERE p.object_id = OBJECT_ID('dbo.usp_PlaceOrder');
GO

Best Practices

• Always SET NOCOUNT ON; use schema prefix (dbo.)
• Do NOT name procs "sp_..." (SQL Server looks in master first)
• Validate input early; use SET XACT_ABORT ON with transactions
• Use SCOPE_IDENTITY() (not @@IDENTITY) to get the new identity value
• Parameter sniffing: plan built for the FIRST parameter values can be bad for others. Fixes: OPTION (RECOMPILE), OPTIMIZE FOR, or local variables.

PRACTICE QUESTIONS 1. Write usp_GiveRaise @DepartmentID, @Percent with validation (0 < percent <= 20) and audit logging. 2. Write usp_CancelOrder @OrderID that sets Status='Cancelled' and returns stock to Products. 3. Write usp_GetSalesSummary @FromDate, @ToDate with OUTPUT params for total revenue and order count. 4. Create a table type for new customers and a proc that bulk inserts them.