Lab: Build a Stored Procedure with Validation + Error Handling

Section 4 — Lab: Build a Stored Procedure with Validation + Error Handling

Welcome to the lab. This is where your skills become job-ready.
You are going to build a stored procedure that looks and behaves like something a real DBA would ship
into production: validated inputs, safe transactions, guardrails, clean outputs, and evidence logging.

The goal is not just “make it work.” The goal is:
make it safe, predictable, and auditable.

Lab description: You will create a stored procedure that generates a customer order summary
and enforces professional safety rules. The procedure will validate inputs, prevent unsafe ranges,
use TRY/CATCH, return both an OUTPUT parameter and a RETURN code, and provide a clear evidence trail.
You will also test success paths and failure paths so you know exactly how it behaves under pressure.

Lab Outcome (What You Will Have When Finished)

• A production-style stored procedure you can reuse as a template in future sections.
• A consistent pattern for validation, transactions, rowcount guardrails, and error handling.
• A caller script that captures return codes and output parameters cleanly.
• Proof that you can write operational SQL, not just queries.

Step 1 — Choose the Lab Dataset

This lab uses the AdventureWorks sample database.
If you do not have it installed yet, you can still follow the pattern in any practice database,
but AdventureWorks is recommended because the schema is realistic.

Step 2 — Define the Procedure Requirements (DBA Specification)

You will build this stored procedure:
dbo.usp_CustomerOrderSummary

Inputs (Parameters)

• @CustomerID int (required)
• @StartDate date (required)
• @EndDate date (required)
• @TopN int = 50 (optional, capped)
• @MaxAllowedDays int = 366 (guardrail)

Outputs

• @ReturnedRows int OUTPUT (how many rows returned)
• Return code (0 = success; non-zero indicates controlled failure category)

Behavior Requirements (Professional Rules)

• Validate @CustomerID is positive and exists.
• Validate @StartDate and @EndDate are not NULL and StartDate <= EndDate.
• Reject ranges larger than @MaxAllowedDays to avoid runaway queries.
• Validate @TopN is between 1 and 500.
• Use a SARGable date filter: Start inclusive, end exclusive.
• Return a clean result set of orders (SalesOrderID, OrderDate, TotalDue).
• Return @ReturnedRows as output and a return code.
• Use TRY/CATCH to fail safely and provide error evidence.

Why this is DBA-grade: This procedure protects the database and supports automation.
It is safe to call from jobs, dashboards, and applications.

Step 3 — Create the Stored Procedure (Copy/Paste Script)

CREATE OR ALTER PROCEDURE dbo.usp_CustomerOrderSummary
    @CustomerID      int,
    @StartDate       date,
    @EndDate         date,
    @TopN            int = 50,
    @MaxAllowedDays  int = 366,
    @ReturnedRows    int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Default OUTPUT value
    SET @ReturnedRows = 0;

    --------------------------------------------------------------------
    -- Evidence header (useful for debugging/job logs)
    --------------------------------------------------------------------
    DECLARE @StartTime datetime2(3) = SYSDATETIME();

    BEGIN TRY
        ----------------------------------------------------------------
        -- 1) Parameter validation (fail fast)
        ----------------------------------------------------------------
        IF @CustomerID IS NULL OR @CustomerID <= 0
            RETURN 10; -- invalid customer id

        IF @StartDate IS NULL OR @EndDate IS NULL
            RETURN 11; -- missing dates

        IF @StartDate > @EndDate
            RETURN 12; -- invalid range

        IF @TopN IS NULL OR @TopN < 1 OR @TopN > 500
            RETURN 13; -- invalid TopN

        IF @MaxAllowedDays IS NULL OR @MaxAllowedDays < 1 OR @MaxAllowedDays > 3660
            RETURN 14; -- invalid guardrail

        DECLARE @RangeDays int = DATEDIFF(DAY, @StartDate, @EndDate) + 1;

        IF @RangeDays > @MaxAllowedDays
            RETURN 15; -- range too large (safety stop)

        ----------------------------------------------------------------
        -- 2) Verify customer exists (controlled failure)
        ----------------------------------------------------------------
        IF NOT EXISTS (SELECT 1 FROM Sales.Customer WHERE CustomerID = @CustomerID)
            RETURN 20; -- customer not found

        ----------------------------------------------------------------
        -- 3) Query logic (SARGable date filter)
        --    EndExclusive = EndDate + 1 day
        ----------------------------------------------------------------
        DECLARE @EndExclusive date = DATEADD(DAY, 1, @EndDate);

        -- Note: This is read-only, so we do not need an explicit transaction.
        -- Transaction would be more appropriate for data changes (INSERT/UPDATE/DELETE).
        -- DBAs keep read-only procedures lean and safe.

        SELECT TOP (@TopN)
            soh.SalesOrderID,
            soh.OrderDate,
            soh.TotalDue
        FROM Sales.SalesOrderHeader AS soh
        WHERE soh.CustomerID = @CustomerID
          AND soh.OrderDate >= @StartDate
          AND soh.OrderDate <  @EndExclusive
        ORDER BY soh.OrderDate DESC, soh.SalesOrderID DESC;

        SET @ReturnedRows = @@ROWCOUNT;

        ----------------------------------------------------------------
        -- 4) Controlled outcome rules
        ----------------------------------------------------------------
        IF @ReturnedRows = 0
            RETURN 30; -- no orders in date range (not an error, but controlled result)

        RETURN 0; -- success
    END TRY
    BEGIN CATCH
        ----------------------------------------------------------------
        -- Catch unexpected errors and return evidence
        ----------------------------------------------------------------
        DECLARE @ErrNumber   int = ERROR_NUMBER();
        DECLARE @ErrSeverity int = ERROR_SEVERITY();
        DECLARE @ErrState    int = ERROR_STATE();
        DECLARE @ErrLine     int = ERROR_LINE();
        DECLARE @ErrProc     nvarchar(128) = ERROR_PROCEDURE();
        DECLARE @ErrMsg      nvarchar(4000) = ERROR_MESSAGE();

        -- Evidence output (you can keep this for debugging or log it to a table later)
        SELECT
            @StartTime        AS ScriptStartTime,
            SYSDATETIME()     AS ScriptEndTime,
            @@SERVERNAME      AS ServerName,
            DB_NAME()         AS DatabaseName,
            SUSER_SNAME()     AS ExecutedBy,
            @CustomerID       AS CustomerID,
            @StartDate        AS StartDate,
            @EndDate          AS EndDate,
            @TopN             AS TopN,
            @MaxAllowedDays   AS MaxAllowedDays,
            @ReturnedRows     AS ReturnedRows,
            @ErrNumber        AS ErrorNumber,
            @ErrSeverity      AS ErrorSeverity,
            @ErrState         AS ErrorState,
            @ErrProc          AS ErrorProcedure,
            @ErrLine          AS ErrorLine,
            @ErrMsg           AS ErrorMessage;

        -- Fail loudly for unexpected errors (preferred in production jobs)
        THROW;
    END CATCH;
END;

Step 4 — Test the Procedure (Caller Scripts)

Test A: Success path

DECLARE @Rows int;
DECLARE @RC   int;

EXEC @RC = dbo.usp_CustomerOrderSummary
    @CustomerID     = 11000,
    @StartDate      = '2014-01-01',
    @EndDate        = '2014-12-31',
    @TopN           = 25,
    @MaxAllowedDays = 366,
    @ReturnedRows   = @Rows OUTPUT;

SELECT
    @RC   AS ReturnCode,
    @Rows AS ReturnedRows;

Test B: Customer not found

DECLARE @Rows int;
DECLARE @RC   int;

EXEC @RC = dbo.usp_CustomerOrderSummary
    @CustomerID     = 999999, -- likely not present
    @StartDate      = '2014-01-01',
    @EndDate        = '2014-12-31',
    @TopN           = 25,
    @MaxAllowedDays = 366,
    @ReturnedRows   = @Rows OUTPUT;

SELECT @RC AS ReturnCode, @Rows AS ReturnedRows;

Test C: Invalid date range

DECLARE @Rows int;
DECLARE @RC   int;

EXEC @RC = dbo.usp_CustomerOrderSummary
    @CustomerID     = 11000,
    @StartDate      = '2014-12-31',
    @EndDate        = '2014-01-01', -- invalid
    @TopN           = 25,
    @MaxAllowedDays = 366,
    @ReturnedRows   = @Rows OUTPUT;

SELECT @RC AS ReturnCode, @Rows AS ReturnedRows;

Test D: Safety stop (range too large)

DECLARE @Rows int;
DECLARE @RC   int;

EXEC @RC = dbo.usp_CustomerOrderSummary
    @CustomerID     = 11000,
    @StartDate      = '2011-01-01',
    @EndDate        = '2014-12-31',
    @TopN           = 25,
    @MaxAllowedDays = 60, -- deliberately strict guardrail
    @ReturnedRows   = @Rows OUTPUT;

SELECT @RC AS ReturnCode, @Rows AS ReturnedRows;

Step 5 — Interpret Return Codes (DBA Documentation)

Document these codes in your DBA notebook:

• 0 = Success (rows returned)
• 10 = Invalid CustomerID
• 11 = Missing StartDate/EndDate
• 12 = StartDate > EndDate
• 13 = Invalid TopN
• 14 = Invalid MaxAllowedDays
• 15 = Range too large (safety stop)
• 20 = Customer not found
• 30 = No orders in range (controlled result)

DBA reason: This makes your procedure automation-friendly and easy to support.

Step 6 — What Makes This “Production-Ready”

• Validates all inputs before doing work (fail fast).
• Uses a SARGable date filter (good performance and correct results).
• Implements guardrails to prevent runaway queries.
• Returns consistent outputs (return code + output parameter).
• Captures evidence in CATCH for troubleshooting.
• Throws unexpected errors so jobs fail loudly (no silent failures).

Lab Completion Checklist

• You created the procedure successfully (no errors).
• You ran the success test and captured ReturnCode = 0 and ReturnedRows > 0.
• You ran the “not found” test and got ReturnCode = 20.
• You ran the invalid range test and got ReturnCode = 12.
• You ran the safety stop test and got ReturnCode = 15.
• You saved all scripts in your lab folder (procedures + caller tests).

Next Section: Section 5 — Lab Setup: Build a DBA-Grade Environment

Next, you will move into environment setup and repeatable installation discipline:
sizing basics, virtualization choices, Windows hardening, SQL Server installation strategy,
and building a reusable DBA install checklist.