SA0054 : Avoid modification of parameters in a stored procedure prior to use in a query |
![]() |
The topic describes the SA0054 analysis rule.

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

For best query performance, in some situations you’ll need to avoid assigning a new value to a parameter of a stored procedure within the procedure body, and then using the parameter value in a query. The stored procedure and all queries in it are initially compiled with the parameter value first passed in as a parameter to the query.

Review the parameter usages and avoid assigning a new value to a parameter of a stored procedure within the procedure body, and then using the parameter value in a query.

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 |
