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

The topic describes the SA0054 analysis rule.

Message

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

Description

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.

How to fix

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.

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