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.

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

Ensure parameters in stored procedures are not reassigned within the procedure body to maintain optimal query performance.
Follow these steps to address the issue:
-
Identify stored procedures where parameters are reassigned. Use SET or SELECT statements that modify parameter values within the procedure.
-
Where possible, avoid modifying parameters directly. Instead, use local variables within the procedure to hold any new values.
-
Update the queries within the procedure to use these local variables instead of the original parameters if a reassignment is necessary.
For example:
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;

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.


Performance Rules, Bugs

There is no additional info for this rule.

SQL
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 |

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