Stored procedures, output parameters, and encapsulated database logic

Overview

Stored procedures are named database modules that encapsulate one or more T-SQL statements behind a callable interface. They can accept input parameters, return result sets, set output parameters, return status codes, enforce permissions, and centralize database-side logic close to the data.

This topic matters because stored procedures are common in enterprise SQL Server systems, reporting workflows, data imports, background jobs, and APIs that treat the database as an explicit contract boundary. A well-designed procedure can reduce duplication, protect tables from direct access, keep multi-step data operations consistent, and provide a stable interface for application code. A poorly designed procedure can hide too much business logic, become hard to test, return ambiguous results, or create performance problems through parameter-sensitive plans.

For interviews, stored procedures test practical database design judgment. Strong candidates can explain when procedures are useful, how input and output parameters work, when to return result sets, how procedures relate to transactions and security, and why encapsulated database logic is a trade-off rather than an automatic win.

Core Concepts

Stored Procedure Basics

A stored procedure is created with CREATE PROCEDURE or CREATE OR ALTER PROCEDURE. It lives in a schema and can be executed by name.

CREATE OR ALTER PROCEDURE sales.GetCustomerOrders
    @CustomerId int,
    @FromDate date = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM sales.Orders
    WHERE CustomerId = @CustomerId
      AND (@FromDate IS NULL OR OrderDate >= @FromDate)
    ORDER BY OrderDate DESC;
END;

Common procedure responsibilities include:

• Read queries used by applications or reports.
• Multi-table writes.
• Data import and cleanup steps.
• Batch processing.
• Permission boundaries.
• Reusable database operations.
• Validation that must happen near the data.

Input Parameters

Input parameters pass values into the procedure. They should be strongly typed and match the underlying columns when used for filtering or joining.

CREATE OR ALTER PROCEDURE sales.GetOrdersByStatus
    @Status varchar(20),
    @StartDate date,
    @EndDate date
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, Status, OrderDate
    FROM sales.Orders
    WHERE Status = @Status
      AND OrderDate >= @StartDate
      AND OrderDate < DATEADD(day, 1, @EndDate);
END;

Good parameter design matters because mismatched types can cause implicit conversions, poor cardinality estimates, and missed index seeks.

Default Parameter Values

Parameters can have defaults. Defaults are useful for optional behavior, but they can also create plan-quality problems when one procedure tries to support too many query shapes.

CREATE OR ALTER PROCEDURE sales.SearchOrders
    @CustomerId int = NULL,
    @Status varchar(20) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, CustomerId, Status, OrderDate
    FROM sales.Orders
    WHERE (@CustomerId IS NULL OR CustomerId = @CustomerId)
      AND (@Status IS NULL OR Status = @Status);
END;

This style is convenient, but optional predicates can produce generic or parameter-sensitive plans. For high-volume search procedures, branching or safe dynamic SQL may be better.

Output Parameters

Output parameters let a procedure assign scalar values that the caller can read after execution.

CREATE OR ALTER PROCEDURE sales.CreateOrder
    @CustomerId int,
    @OrderId bigint OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT sales.Orders (CustomerId, OrderDate)
    VALUES (@CustomerId, SYSUTCDATETIME());

    SET @OrderId = CONVERT(bigint, SCOPE_IDENTITY());
END;

Caller:

DECLARE @NewOrderId bigint;

EXEC sales.CreateOrder
    @CustomerId = 42,
    @OrderId = @NewOrderId OUTPUT;

SELECT @NewOrderId AS NewOrderId;

Use output parameters for scalar outputs such as generated IDs, counts, status details, or calculated values. Use result sets for rows and collections.

Return Codes Vs Output Parameters Vs Result Sets

Stored procedures can communicate in several ways:

Avoid using return codes for rich business data. Return codes are integer-only and are easy for application code to ignore. For most failures, throwing an error is clearer than returning a magic number.

Encapsulated Database Logic

Encapsulated database logic means hiding a database operation behind a stable procedure contract.

Benefits include:

• Application code calls one named operation.
• Table structure can change behind the procedure.
• Permissions can be granted on the procedure instead of base tables.
• Common logic is implemented once.
• Multi-table operations can stay close to the data.
• Data-heavy work can avoid moving unnecessary rows to the application.

Trade-offs include:

• Logic can become split between application and database.
• Procedures can become large and procedural.
• Unit testing may be harder than application code testing.
• Versioning procedure contracts requires care.
• Debugging can require database-specific tools and skills.

Security Boundaries

Stored procedures can reduce the need to grant direct table permissions. A user or application can receive EXECUTE permission on a procedure without being able to query or modify the underlying tables directly.

GRANT EXECUTE ON sales.CreateOrder TO app_order_writer;

Procedures can also use execution context options such as EXECUTE AS. This can be useful, but it must be designed carefully because modules that run with elevated permissions can become security risks.

SQL Injection And Dynamic SQL

Stored procedures do not automatically prevent SQL injection. Parameters help when they are used as values, but dynamic SQL can still be unsafe if user input is concatenated into executable text.

Unsafe:

SET @sql = N'SELECT * FROM sales.Orders WHERE Status = ''' + @Status + N'''';
EXEC (@sql);

Safer:

SET @sql = N'
SELECT OrderId, Status, OrderDate
FROM sales.Orders
WHERE Status = @Status';

EXEC sys.sp_executesql
    @sql,
    N'@Status varchar(20)',
    @Status = @Status;

Dynamic SQL should parameterize values and strictly validate identifiers when identifiers must be dynamic.

Plan Reuse And Parameter Sensitivity

Procedures can benefit from plan reuse. SQL Server can cache an execution plan and reuse it for later executions. This reduces compilation overhead, but it can cause performance problems when different parameter values need different plans.

Symptoms include:

• Procedure is fast for some parameters and slow for others.
• Recompiling temporarily changes performance.
• Query Store shows multiple plans or high variation.
• Estimated row counts differ greatly from actual row counts.

Possible mitigations include better indexes, updated statistics, branching, safe dynamic SQL, OPTION (RECOMPILE), OPTIMIZE FOR, or modern parameter-sensitive plan features.

SET NOCOUNT ON

SET NOCOUNT ON suppresses row count messages after statements. It is common in stored procedures because application callers usually care about result sets and output parameters, not intermediate row count messages.

CREATE OR ALTER PROCEDURE dbo.DoWork
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.WorkItem
    SET ProcessedAt = SYSUTCDATETIME()
    WHERE ProcessedAt IS NULL;
END;

It does not stop the procedure from returning actual result sets.

Result Set Contracts

If application code depends on a procedure result set, treat that shape as a contract.

Contract details include:

• Column names.
• Column order.
• Data types.
• Nullability expectations.
• Row meaning.
• Sorting guarantees, if any.

Changing a result set can break application code even if the procedure still executes successfully.

Common Mistakes

Common mistakes include:

• Returning many scalar values through output parameters when a result set would be clearer.
• Using return codes for normal business data.
• Swallowing errors inside procedures.
• Concatenating user input into dynamic SQL.
• Creating large procedural modules that are hard to test.
• Using sp_ prefixes for user procedures.
• Hiding critical business rules in undocumented procedures.
• Creating optional-parameter search procedures without testing plan quality.
• Changing procedure result sets without versioning or coordinating callers.

Best Practices

Best practices include:

• Keep procedure contracts clear and stable.
• Use input parameters with correct data types.
• Use output parameters for scalar outputs.
• Use result sets for tabular data.
• Use SET NOCOUNT ON.
• Throw errors for exceptional failures.
• Parameterize dynamic SQL.
• Keep procedures cohesive and reasonably small.
• Grant EXECUTE permissions instead of broad table access where appropriate.
• Test procedures with representative parameter values.
• Source-control procedure definitions and review changes like application code.