Stored Procedure Parameters in SQL Server, Explained

Most stored procedure articles jump straight to parameter sniffing, the optimizer picking a bad plan for a skewed parameter value. That is a real and separate problem, covered in Parameter Sniffing in SQL Server. This article stays one level below that: what a parameter actually is, the ways to declare and pass one, and the mistakes that cost performance or correctness before the optimizer even gets involved.

The Basics

CREATE PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId INT,
    @Status     VARCHAR(20) = 'Open',   -- default value, parameter becomes optional
    @MaxRows    INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (ISNULL(@MaxRows, 1000)) OrderId, OrderDate, Total
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
      AND Status = @Status
    ORDER BY OrderDate DESC;
END

Three calling conventions all work, but only one of them ages well:

-- Positional: fragile, breaks silently if the parameter order ever changes
EXEC dbo.GetOrdersByCustomer 47, 'Shipped';

-- Named: explicit, safe to reorder, self-documenting at the call site
EXEC dbo.GetOrdersByCustomer @CustomerId = 47, @Status = 'Shipped';

-- Named, skipping an optional parameter entirely
EXEC dbo.GetOrdersByCustomer @CustomerId = 47;

Positional calls are the more common source of a quiet bug: add a new parameter in the middle of the signature, and every existing positional caller now binds the wrong values to the wrong parameters, with no error unless a type mismatch happens to throw one. Named parameters cost a few extra keystrokes and remove that entire failure mode.

OUTPUT Parameters

A result set is not the only way to get data out of a procedure. OUTPUT parameters return a single value (or a few) back into a variable at the call site, without the overhead of a result set the caller has to consume:

CREATE PROCEDURE dbo.GetCustomerOrderCount
    @CustomerId INT,
    @OrderCount INT OUTPUT
AS
BEGIN
    SELECT @OrderCount = COUNT(*)
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END

-- Calling it:
DECLARE @Count INT;
EXEC dbo.GetCustomerOrderCount @CustomerId = 47, @OrderCount = @Count OUTPUT;
SELECT @Count;

Forgetting the OUTPUT keyword on the call is a classic mistake: SQL Server does not error, it just silently treats the parameter as input-only, and @Count stays whatever it was before the call (typically NULL). If a caller depends on an output parameter, verify the call site has OUTPUT on both the parameter declaration and the EXEC statement.

RETURN vs. OUTPUT vs. a Result Set

RETURN is easy to misuse as a data channel because it looks convenient. It is typed as INT only and exists for status codes; reach for an OUTPUT parameter the moment the value you need to return is not a status.

Table-Valued Parameters

Before table-valued parameters (TVPs), passing a set of rows into a procedure meant a delimited string that got parsed apart inside the procedure, or one round trip per row. Neither scales. A TVP passes an entire table as a single strongly-typed parameter, in one call:

-- 1. Define a reusable table type once
CREATE TYPE dbo.OrderIdList AS TABLE
(
    OrderId INT PRIMARY KEY
);
GO

-- 2. Use it as a parameter type
CREATE PROCEDURE dbo.CancelOrders
    @Ids dbo.OrderIdList READONLY   -- TVPs must be READONLY
AS
BEGIN
    UPDATE o
    SET Status = 'Cancelled'
    FROM dbo.Orders o
    JOIN @Ids i ON i.OrderId = o.OrderId;
END

Two things trip people up the first time: the parameter must be declared READONLY (you cannot modify a TVP inside the procedure), and the table type is a schema object created once with CREATE TYPE, not declared inline per call. In .NET code, a DataTable or an IEnumerable<SqlDataRecord> maps directly onto a TVP parameter, avoiding both string-splitting and row-by-row calls entirely.

The Data Type Mismatch Trap

This is the parameter mistake with the highest cost-to-obviousness ratio: declaring a parameter with a data type that does not exactly match the column it filters. SQL Server does not just tolerate the mismatch, it silently converts one side, and depending on which side gets converted, an otherwise perfectly indexed query stops seeking and starts scanning.

This is easy to introduce from application code: an ORM or a data access layer that defaults string parameters to NVARCHAR regardless of the underlying column type is a common source of it, and the symptom looks nothing like a type problem, it looks like a missing index, because the execution plan shows a scan on a column that clearly has an index. Check the parameter declaration and the CONVERT_IMPLICIT warning in the actual execution plan before adding an index that already exists.

Dynamic SQL: Parameterize It, Do Not Concatenate It

When the query shape itself needs to vary (optional filters, dynamic sort columns), dynamic SQL is often the right tool, but the difference between sp_executesql and string concatenation is not stylistic:

-- Vulnerable to SQL injection AND generates a unique plan per literal value
DECLARE @sql NVARCHAR(MAX) =
    N'SELECT * FROM dbo.Orders WHERE CustomerId = ' + CAST(@CustomerId AS NVARCHAR(10));
EXEC (@sql);

-- Parameterized: safe from injection, and the plan is reusable across calls
DECLARE @sql NVARCHAR(MAX) =
    N'SELECT * FROM dbo.Orders WHERE CustomerId = @CustomerId';
EXEC sp_executesql @sql, N'@CustomerId INT', @CustomerId = @CustomerId;

Concatenating a value directly into the SQL text does two kinds of damage at once: it opens the classic SQL injection vector, and it defeats plan reuse, because every distinct literal value produces a distinct query text and therefore its own cache entry, flooding the plan cache with one-off plans that are never reused. sp_executesql with a parameter placeholder fixes both problems the same way: the value travels as data, not as text.

WITH RECOMPILE: Procedure-Level vs. Query-Level

WITH RECOMPILE can sit on the procedure itself or on OPTION (RECOMPILE) at the end of an individual statement, and the difference in blast radius matters:

-- Procedure-level: every statement in the procedure recompiles, every call
CREATE PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId INT
WITH RECOMPILE
AS
    SELECT * FROM dbo.Orders WHERE CustomerId = @CustomerId;

-- Statement-level: only this one query recompiles, the rest of the procedure's plan is untouched
CREATE PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId INT
AS
    SELECT * FROM dbo.Orders WHERE CustomerId = @CustomerId
    OPTION (RECOMPILE);

Procedure-level WITH RECOMPILE also means the plan is never cached at all, not even for the duration of one execution shared across a batch, so it is rarely the right default. Statement-level OPTION (RECOMPILE) is the narrower, usually correct choice when only one query inside a larger procedure has a genuine sniffing or skew problem.

The Bottom Line

Most of what goes wrong with stored procedure parameters has nothing to do with the optimizer: a forgotten OUTPUT keyword that silently returns nothing, a positional call that breaks when the signature changes, a TVP saving a thousand round trips, or a parameter declared one data type off from its column quietly turning every seek into a scan. Get the parameter mechanics right first. Once that is solid, and a specific query still runs inconsistently depending on which value triggered the last compile, that is the separate, narrower problem covered in Parameter Sniffing in SQL Server and Parameter Sniffing: When Good Plans Go Bad.