Lesson 7: Stored Procedures (Parameters, Output Parameters, Return Codes)

Section 4 — Lesson 7: Stored Procedures (Parameters, Output Parameters, Return Codes)

Welcome back. Stored procedures are one of the most common “real-world” objects a DBA works with.
Even if you are not a developer, you will support stored procedures constantly:
performance tuning, troubleshooting failures, reviewing risky changes, handling permissions,
and building operational procedures for maintenance, reporting, and automation.

In this lesson you will learn how DBAs design and use stored procedures in a professional way:
predictable inputs, predictable outputs, clear success/failure signals, and safe behavior in production.

Lesson description: You will learn what stored procedures are and when DBAs use them,
how to create procedures with input parameters, how to validate parameters defensively,
how to use OUTPUT parameters to return values to the caller,
how to use RETURN codes to indicate success/failure,
how to capture and return rowcounts safely,
and how to combine stored procedures with TRY/CATCH + transactions for job-ready operational scripting.

1) What a Stored Procedure Is (DBA Definition)

A stored procedure (often called a “proc”) is a saved, executable T-SQL program in the database.
Instead of copying and pasting the same query logic everywhere, a procedure lets you:

• standardize business logic and operational logic
• accept inputs (parameters)
• return consistent results and status codes
• control permissions (execute proc without giving direct table access)
• reduce mistakes by packaging safe patterns once

DBA reality: Many SQL Agent jobs call stored procedures.
When a job fails, DBAs often start by inspecting the procedure.

2) Parameters: Inputs to Your Procedure

Parameters let callers pass values into the procedure. DBAs care about parameters because:

• parameters impact performance (plan choice and parameter sniffing)
• parameters must be validated to prevent dangerous actions
• procedures should behave safely even with unexpected inputs

2A) Simple procedure with input parameter (read-only example)

CREATE OR ALTER PROCEDURE dbo.usp_GetOrdersByCustomer
    @CustomerID int
AS
BEGIN
    SET NOCOUNT ON;

    -- Defensive parameter validation
    IF @CustomerID IS NULL OR @CustomerID <= 0
        THROW 52001, 'Invalid CustomerID. Must be a positive integer.', 1;

    SELECT TOP (200)
        soh.SalesOrderID,
        soh.OrderDate,
        soh.TotalDue
    FROM Sales.SalesOrderHeader AS soh
    WHERE soh.CustomerID = @CustomerID
    ORDER BY soh.OrderDate DESC, soh.SalesOrderID DESC;
END;

DBA habit: Always include SET NOCOUNT ON; in procs to avoid noisy “(X rows affected)”
output that can confuse job logs and API callers.

3) OUTPUT Parameters: Returning Values to the Caller

A procedure can return a result set (SELECT rows), but sometimes you want to return a single value
such as a count, a generated ID, or a status message. OUTPUT parameters are designed for this.

3A) Example: Return the number of orders for a customer

CREATE OR ALTER PROCEDURE dbo.usp_GetCustomerOrderCount
    @CustomerID int,
    @OrderCount int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF @CustomerID IS NULL OR @CustomerID <= 0
        THROW 52002, 'Invalid CustomerID. Must be a positive integer.', 1;

    SELECT @OrderCount = COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID;
END;

3B) How to call a procedure with OUTPUT parameter

DECLARE @Count int;

EXEC dbo.usp_GetCustomerOrderCount
    @CustomerID = 11000,
    @OrderCount = @Count OUTPUT;

SELECT @Count AS OrderCount;

DBA note: OUTPUT parameters are extremely common in operational procedures that drive jobs and automation.

4) RETURN Codes: Success/Failure Signal (DBA Standard)

In SQL Server, a stored procedure can return an integer value using RETURN.
This is called the return code. It is typically used to indicate:

• 0 = Success
• non-zero = Failure (different numbers can represent different failures)

DBA standard: Keep return codes simple, consistent, and documented.
SQL Agent jobs and automation scripts can check these codes.

4A) Example: Return code with defensive checks

CREATE OR ALTER PROCEDURE dbo.usp_ValidateCustomerExists
    @CustomerID int
AS
BEGIN
    SET NOCOUNT ON;

    IF @CustomerID IS NULL OR @CustomerID <= 0
        RETURN 10; -- Invalid input

    IF NOT EXISTS (SELECT 1 FROM Sales.Customer WHERE CustomerID = @CustomerID)
        RETURN 20; -- Not found

    RETURN 0; -- Success
END;

4B) How to capture a RETURN code

DECLARE @RC int;

EXEC @RC = dbo.usp_ValidateCustomerExists
    @CustomerID = 11000;

SELECT @RC AS ReturnCode;

DBA best practice: Use RETURN codes for high-level status (success/failure categories),
and use error throwing (THROW) for detailed error messages when you want the caller/job to fail loudly.

5) A Job-Ready Stored Procedure Pattern (TRY/CATCH + Transaction + Return Code)

This is a professional template you can reuse for operational procedures.
It provides:

• validated inputs
• safe transaction handling
• clean error capture
• rowcount evidence
• return code for automation

5A) Template

CREATE OR ALTER PROCEDURE dbo.usp_SafeUpdateTemplate
    @SomeID int,
    @MaxAllowedRows int = 100,
    @RowsAffected int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Default outputs
    SET @RowsAffected = 0;

    BEGIN TRY
        -- Defensive parameter validation
        IF @SomeID IS NULL OR @SomeID <= 0
            RETURN 10; -- invalid input

        IF @MaxAllowedRows IS NULL OR @MaxAllowedRows <= 0 RETURN 11; -- invalid guardrail BEGIN TRAN; -- ====== DO THE WORK (placeholder) ====== -- UPDATE dbo.SomeTable -- SET SomeColumn = 'X' -- WHERE SomeID = @SomeID; -- Demo rowcount (replace with @@ROWCOUNT after your real statement) SET @RowsAffected = 1; -- ====== GUARDRAILS ====== IF @RowsAffected = 0 BEGIN -- nothing changed, treat as safe failure (or success depending on design) ROLLBACK TRAN; RETURN 20; -- not found / no rows affected END; IF @RowsAffected > @MaxAllowedRows
        BEGIN
            ROLLBACK TRAN;
            RETURN 30; -- safety stop
        END;

        COMMIT TRAN;
        RETURN 0; -- success
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRAN;

        -- Option A: return a generic failure code (keep job logs clean)
        -- RETURN 99;

        -- Option B (common for DBAs): raise the error so the job fails loudly with details
        THROW;
    END CATCH
END;

DBA design note: There are two common strategies:

• Return-code strategy: procedure returns codes; caller handles logic.
• Throw strategy: procedure throws errors for failures; caller/job fails visibly (common for production safety).

6) Rowcounts and “Proof” Outputs (Professional DBA Behavior)

When a procedure changes data, DBAs want evidence:
how many rows changed, and what operation happened.
Output parameters are ideal for this because they are easy for jobs and scripts to capture.

6A) Example: procedure returns both a result set and an output value

CREATE OR ALTER PROCEDURE dbo.usp_GetRecentOrdersWithCount
    @CustomerID int,
    @TopN int = 25,
    @ReturnedRows int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF @CustomerID IS NULL OR @CustomerID <= 0
        THROW 52003, 'Invalid CustomerID.', 1;

    IF @TopN IS NULL OR @TopN <= 0 OR @TopN > 500
        THROW 52004, 'TopN must be between 1 and 500.', 1;

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

    SET @ReturnedRows = @@ROWCOUNT;
END;

7) DBA Safety Notes (Common Procedure Pitfalls)

7A) Parameter sniffing (preview)

Some procedures perform well for some parameter values and poorly for others due to plan reuse.
DBAs troubleshoot this later in performance-tuning modules.
For now: understand that parameter design impacts plan choice.

7B) Permission strategy

DBAs often grant users permission to EXECUTE a stored procedure without granting direct access
to underlying tables. This supports least privilege.

7C) Uncontrolled procedures in jobs

Procedures called by SQL Agent jobs should:

• log evidence (start/end, rowcounts)
• use TRY/CATCH
• use transactions for data changes
• fail clearly (return code or THROW)

8) Practice (Job-Ready Exercises)

Create these procedures in your lab database (you can use AdventureWorks or a practice DB):

1. Write dbo.usp_GetOrdersByCustomer with a CustomerID parameter and validation.
2. Write dbo.usp_GetCustomerOrderCount with an OUTPUT parameter that returns the count.
3. Write dbo.usp_ValidateCustomerExists that returns codes (0 success, 10 invalid input, 20 not found).
4. Build a safe “template” procedure with TRY/CATCH + transaction + rowcount guardrail and an OUTPUT parameter.
5. Write a caller script that captures both: return code and OUTPUT parameter values, and prints a clean summary.

Lesson Summary

• Stored procedures package logic for reuse, control, and consistent behavior.
• Parameters are inputs; DBAs validate them defensively to prevent risky execution.
• OUTPUT parameters return values (counts, IDs, results) to callers cleanly.
• RETURN codes provide a simple success/failure signal that jobs and automation can check.
• Job-ready procedures use TRY/CATCH + transactions + guardrails and produce evidence (rowcounts, outcomes).

Next Lesson: Lab — Build a Stored Procedure with Validation + Error Handling

Next, you will implement a complete stored procedure in your lab that enforces validation,
uses a transaction, returns outputs, and produces evidence exactly like a production DBA script.