SA0054 : Avoid modification of parameters in a stored procedure prior to use in a query

Introduction

Modifying input parameter within a stored procedure then using it in a query can lead to performance issues.

Description

When using stored procedures in SQL Server, it is essential to consider how parameters are handled within the procedure body. Reassigning a new value to a parameter and then using it in a query can lead to performance issues. The stored procedure and its queries are compiled upon first execution using the initial parameter values. Changing those values later can result in inefficient query execution plans.

For example:

CREATE PROCEDURE GetEmployeeData
    @EmployeeID INT
AS
BEGIN
    -- Reassigning the parameter
    SET @EmployeeID = 123;

    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

In this example, the parameter @EmployeeID is reassigned a new value within the procedure. This practice can cause SQL Server to use a suboptimal execution plan because the procedure was compiled with the original parameter value. This can lead to:

Suboptimal query performance due to plan mismatches.
Difficulties in maintaining and predicting query behavior as the actual data being retrieved can differ from expected outcomes.

How to fix

Ensure parameters in stored procedures are not reassigned within the procedure body to maintain optimal query performance.

Follow these steps to address the issue:

1.Identify stored procedures where parameters are reassigned. Use SET or SELECT statements that modify parameter values within the procedure.

2.Where possible, avoid modifying parameters directly. Instead, use local variables within the procedure to hold any new values.

3.Update the queries within the procedure to use these local variables instead of the original parameters if a reassignment is necessary.

For example:

-- Corrected stored procedure without parameter reassignment
CREATE PROCEDURE GetEmployeeData
    @EmployeeID INT
AS
BEGIN
    DECLARE @LocalEmployeeID INT;
    -- Use a local variable for reassignment
    SET @LocalEmployeeID = 123;

    SELECT * FROM Employees WHERE EmployeeID = @LocalEmployeeID;
END;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE PROCEDURE GetRecentSales
(
    @date AS DATETIME
  , @MinTotal AS INT
)
WITH  RECOMPILE
AS
BEGIN
    IF @date IS NULL

  -- Parmeter @date's value is changed just before it is used in a query.
    SET @date = dateadd( [mm], - 3, ( SELECT      MAX( OrderDATE )
                                      FROM        Sales.SalesOrderHeader ) )

    SELECT      *
    FROM        Sales.SalesOrderHeader AS h
              , Sales.SalesOrderDetail AS d
    WHERE       h.SalesOrderID = d.SalesOrderID AND
                h.SaleTotal >= @MinTotal AND
                h.OrderDate > @date
END

Analysis Results

	Message	Line	Column
1	SA0054 : Parameter @date modification prior to use in a query, may negatively affect performance.	12	8

Analysis Rules