Stored Procedures

A stored procedure is a named, compiled, and stored T-SQL program that lives inside the database. You write it once, give it a name, and call it by that name — with parameters if needed — from any application, script, or other procedure that has permission to execute it. Stored procedures serve three distinct purposes that justify their use over sending raw SQL from application code: they centralise business logic at the database level so that every application consuming the data uses the same validated rules, they improve performance because SQL Server compiles and caches execution plans on first execution and reuses them on subsequent calls, and they enforce security by letting you grant EXECUTE permission on a procedure without granting direct SELECT, INSERT, UPDATE, or DELETE permission on the underlying tables. This lesson covers creating, modifying, and managing stored procedures in full — parameters, output values, error handling, return codes, and the key behaviours that distinguish well-written procedures from fragile ones.

Creating a Basic Stored Procedure

The CREATE PROCEDURE statement defines the procedure name and body. The body is any valid T-SQL — SELECT, INSERT, UPDATE, DELETE, control flow, transactions, or any combination. A procedure without parameters is essentially a named query. The convention is to prefix procedure names with usp_ (user stored procedure) to distinguish them from system procedures. Once created, a procedure is called with EXEC or EXECUTE followed by the procedure name. Like views, the preferred deployment syntax is CREATE OR ALTER PROCEDURE, which creates if absent or replaces if present — making scripts safely re-runnable.

-- Create a simple stored procedure — product sales summary
USE DataplexaStore;

CREATE OR ALTER PROCEDURE usp_GetProductSalesSummary
AS
BEGIN
    SET NOCOUNT ON;     -- suppresses "n rows affected" messages — cleaner for callers

    SELECT
        p.ProductID,
        p.ProductName,
        p.Category,
        p.Price,
        p.StockQty,
        COUNT(oi.OrderItemID)                   AS times_ordered,
        ISNULL(SUM(oi.Quantity), 0)             AS units_sold,
        ISNULL(SUM(oi.Quantity * oi.UnitPrice), 0) AS revenue
    FROM       Products    p
    LEFT JOIN  OrderItems  oi ON p.ProductID = oi.ProductID
    GROUP BY   p.ProductID, p.ProductName, p.Category, p.Price, p.StockQty
    ORDER BY   revenue DESC;
END;

-- Execute the procedure
EXEC usp_GetProductSalesSummary;

-- Inspect stored procedure definitions
SELECT
    OBJECT_NAME(object_id)      AS procedure_name,
    create_date,
    modify_date
FROM   sys.procedures
WHERE  name LIKE 'usp_%'
ORDER BY name;

• SET NOCOUNT ON is the first line in almost every stored procedure — without it every INSERT, UPDATE, and DELETE inside the procedure sends a "n rows affected" message to the caller, which clutters output and can interfere with some ORM frameworks that parse result sets
• The procedure is stored in sys.procedures and its full SQL text is in sys.sql_modules — the same catalog views used for views, making it straightforward to retrieve procedure definitions programmatically
• Plan caching applies immediately — the first EXEC compiles the plan; subsequent EXECs reuse the cached plan without recompiling, which is one of the core performance benefits of stored procedures over ad-hoc SQL

Input Parameters

Parameters are declared after the procedure name with a name, data type, and optional default value. Input parameters allow callers to pass values into the procedure, making a single procedure serve many use cases — one procedure for all product categories rather than one procedure per category. Parameters with default values are optional at call time — the default is used if the caller omits them. Named parameter syntax (@param = value) is safer than positional syntax because it is unaffected by parameter order changes when the procedure is later modified. Always validate input parameters at the top of the procedure — never trust that the caller has passed a sane value.

-- Stored procedure with input parameters and defaults

CREATE OR ALTER PROCEDURE usp_GetProductsByCategory
    @Category       NVARCHAR(50),
    @MinPrice       DECIMAL(10,2) = 0,          -- optional: default 0 (no lower limit)
    @MaxPrice       DECIMAL(10,2) = 999999,     -- optional: default high (no upper limit)
    @SortBy         NVARCHAR(20)  = 'Price'     -- optional: default sort column
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate parameters
    IF @Category IS NULL OR LEN(TRIM(@Category)) = 0
    BEGIN
        RAISERROR('Category parameter cannot be NULL or empty.', 16, 1);
        RETURN;
    END

    IF @MinPrice < 0 OR @MaxPrice < 0 OR @MinPrice > @MaxPrice
    BEGIN
        RAISERROR('Price range is invalid.', 16, 1);
        RETURN;
    END

    SELECT
        ProductID,
        ProductName,
        Category,
        Price,
        StockQty
    FROM   Products
    WHERE  Category = @Category
      AND  Price BETWEEN @MinPrice AND @MaxPrice
    ORDER BY
        CASE WHEN @SortBy = 'Price'    THEN Price        END ASC,
        CASE WHEN @SortBy = 'Stock'    THEN StockQty     END DESC,
        CASE WHEN @SortBy = 'Name'     THEN ProductName  END ASC;
END;

-- Call with all parameters named
EXEC usp_GetProductsByCategory
    @Category = 'Electronics',
    @MinPrice = 30,
    @MaxPrice = 100,
    @SortBy   = 'Price';

-- Call with only the required parameter — defaults apply
EXEC usp_GetProductsByCategory @Category = 'Home Office';

-- Invalid call — triggers RAISERROR
EXEC usp_GetProductsByCategory @Category = '';

• Named parameter syntax (@Category = 'Electronics') is always preferred over positional — if a developer later adds a new parameter between @Category and @MinPrice, positional calls silently pass the wrong values to the wrong parameters
• The CASE expression in ORDER BY is the standard T-SQL pattern for dynamic sorting — a single query handles multiple sort options without dynamic SQL; the limitation is that all CASE branches must return compatible types
• RAISERROR with severity 16 generates a non-fatal error that the caller can catch — severity 20+ would terminate the connection; 16 is the conventional severity for application-level validation errors

Output Parameters and Return Values

A stored procedure can return data in three ways: a result set (rows from a SELECT), output parameters (values written back to the caller's variables), and a return code (an integer from the RETURN statement). Result sets are for multi-row data. Output parameters are for single scalar values that the caller needs — a generated ID, a calculated total, a status flag. The return code is conventionally used to signal success (0) or a specific error category (non-zero), though TRY...CATCH has largely replaced return codes for error handling. Output parameters are declared with the OUTPUT keyword in both the procedure definition and the EXEC call.

-- Procedure with output parameters — place an order and return the new OrderID and total

CREATE OR ALTER PROCEDURE usp_PlaceOrder
    @UserID         INT,
    @ProductID      INT,
    @Quantity       INT,
    @NewOrderID     INT             OUTPUT,     -- returns the generated OrderID
    @OrderTotal     DECIMAL(10,2)   OUTPUT      -- returns the calculated total
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @UnitPrice  DECIMAL(10,2);
    DECLARE @StockQty   INT;

    -- Validate: product must exist and have sufficient stock
    SELECT @UnitPrice = Price, @StockQty = StockQty
    FROM   Products
    WHERE  ProductID = @ProductID;

    IF @UnitPrice IS NULL
    BEGIN
        RAISERROR('Product %d does not exist.', 16, 1, @ProductID);
        RETURN -1;      -- return code -1: product not found
    END

    IF @StockQty < @Quantity
    BEGIN
        RAISERROR('Insufficient stock. Available: %d, Requested: %d', 16, 1, @StockQty, @Quantity);
        RETURN -2;      -- return code -2: insufficient stock
    END

    BEGIN TRANSACTION;
    BEGIN TRY

        -- Insert the order
        INSERT INTO Orders (UserID, TotalAmount, Status)
        VALUES (@UserID, 0, 'processing');

        SET @NewOrderID = SCOPE_IDENTITY();

        -- Insert the order line
        INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice)
        VALUES (@NewOrderID, @ProductID, @Quantity, @UnitPrice);

        -- Deduct stock
        UPDATE Products
        SET    StockQty = StockQty - @Quantity
        WHERE  ProductID = @ProductID;

        -- Calculate and update total
        SET @OrderTotal = @Quantity * @UnitPrice;

        UPDATE Orders
        SET    TotalAmount = @OrderTotal
        WHERE  OrderID = @NewOrderID;

        COMMIT TRANSACTION;
        RETURN 0;       -- return code 0: success

    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        RAISERROR('Order placement failed: %s', 16, 1, ERROR_MESSAGE());
        RETURN -99;
    END CATCH;
END;

-- Call the procedure and capture output parameters
DECLARE @OrderID    INT;
DECLARE @Total      DECIMAL(10,2);
DECLARE @ReturnCode INT;

EXEC @ReturnCode = usp_PlaceOrder
    @UserID     = 3,
    @ProductID  = 8,
    @Quantity   = 2,
    @NewOrderID = @OrderID  OUTPUT,
    @OrderTotal = @Total    OUTPUT;

SELECT
    @ReturnCode AS return_code,
    @OrderID    AS new_order_id,
    @Total      AS order_total;

• return_code = 0 confirms success — the calling script can branch on this value: zero means proceed, non-zero means handle the specific failure code
• @NewOrderID is populated inside the procedure via SCOPE_IDENTITY() and written back to the caller's variable through the OUTPUT parameter — the caller now has the new OrderID without running a second query
• The transaction wraps all three DML statements — if the stock deduction fails after the order was inserted, the CATCH block rolls back everything; the caller receives RAISERROR with the specific error message and return code -99

Parameter Sniffing and OPTION (RECOMPILE)

When SQL Server compiles a stored procedure it builds a plan based on the parameter values passed on the first execution — this is called parameter sniffing. The plan is cached and reused for all subsequent calls regardless of what parameter values they pass. If the first call used a highly selective value (one row) and built a Nested Loops plan, subsequent calls passing a non-selective value (returning 50,000 rows) reuse the same Nested Loops plan even though a Hash Match would be far better. This is one of the most common and most confusing performance problems with stored procedures. OPTION (RECOMPILE) at the query level forces a fresh plan for every execution — eliminating the stale plan problem at the cost of recompilation overhead on each call.

-- Parameter sniffing demonstration and solutions

-- Procedure with parameter sniffing vulnerability
CREATE OR ALTER PROCEDURE usp_GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;

    -- If first call uses Status = 'processing' (1 row), plan is optimised for 1 row
    -- Later calls with Status = 'delivered' (many rows) reuse the same narrow plan
    SELECT
        o.OrderID,
        o.OrderDate,
        o.TotalAmount,
        u.FullName
    FROM   Orders o
    JOIN   Users  u ON o.UserID = u.UserID
    WHERE  o.Status = @Status
    ORDER BY o.OrderDate DESC;
END;

-- Solution 1: OPTION (RECOMPILE) — fresh plan per execution
CREATE OR ALTER PROCEDURE usp_GetOrdersByStatus_Safe
    @Status NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        o.OrderID,
        o.OrderDate,
        o.TotalAmount,
        u.FullName
    FROM   Orders o
    JOIN   Users  u ON o.UserID = u.UserID
    WHERE  o.Status = @Status
    ORDER BY o.OrderDate DESC
    OPTION (RECOMPILE);    -- new plan on every call — correct for highly variable parameters
END;

-- Solution 2: LOCAL variable trick — breaks parameter sniffing by hiding the value
CREATE OR ALTER PROCEDURE usp_GetOrdersByStatus_Local
    @Status NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @LocalStatus NVARCHAR(20) = @Status;  -- optimiser cannot sniff through locals

    SELECT
        o.OrderID,
        o.OrderDate,
        o.TotalAmount,
        u.FullName
    FROM   Orders o
    JOIN   Users  u ON o.UserID = u.UserID
    WHERE  o.Status = @LocalStatus
    ORDER BY o.OrderDate DESC;
END;

EXEC usp_GetOrdersByStatus_Safe @Status = 'delivered';

• OPTION (RECOMPILE) is the correct choice when parameter values have highly variable selectivity — a Status value that returns 1 row versus one that returns 500,000 rows genuinely needs different plans; the recompilation cost (typically microseconds) is far less than the cost of executing a wrong plan
• The local variable trick forces the optimiser to build a plan based on average selectivity (derived from statistics density) rather than the specific sniffed value — useful when OPTION (RECOMPILE) overhead is unacceptable on very high-frequency procedures
• Parameter sniffing is not always bad — when parameter values have similar distributions (most calls return a similar number of rows) the cached plan is appropriate for all calls and plan reuse is a genuine performance benefit

Summary Table

Practice Questions

Quiz

Next up - User-Defined Functions - Write scalar and table-valued functions that encapsulate reusable calculations and return values directly inside queries.