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

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:

SQL
Copy
1CREATE PROCEDURE GetEmployeeData
2    @EmployeeID INT
3AS
4BEGIN
5    -- Reassigning the parameter
6    SET @EmployeeID = 123;
7
8    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
9END;

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:

SQL
Copy
 1-- Corrected stored procedure without parameter reassignment
 2CREATE PROCEDURE GetEmployeeData
 3    @EmployeeID INT
 4AS
 5BEGIN
 6    DECLARE @LocalEmployeeID INT;
 7    -- Use a local variable for reassignment
 8    SET @LocalEmployeeID = 123;
 9
10    SELECT * FROM Employees WHERE EmployeeID = @LocalEmployeeID;
11END;

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.
Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE PROCEDURE GetRecentSales
 2( 
 3    @date AS DATETIME
 4  , @MinTotal AS INT
 5)
 6WITH  RECOMPILE
 7AS 
 8BEGIN
 9    IF @date IS NULL
10
11        -- Parmeter @date's value is changed just before it is used in a query.
12    SET @date = dateadd( [mm], - 3, ( SELECT      MAX( OrderDATE )
13                                      FROM        Sales.SalesOrderHeader ) )
14
15    SELECT      *
16    FROM        Sales.SalesOrderHeader AS h
17              , Sales.SalesOrderDetail AS d
18    WHERE       h.SalesOrderID = d.SalesOrderID AND
19                h.SaleTotal >= @MinTotal AND
20                h.OrderDate > @date
21END
Analysis Results
  Message Line Column
1 SA0054 : Parameter @date modification prior to use in a query, may negatively affect performance. 12 8
See Also

Other Resources

Analysis Rules