Click or drag to resize

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.

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.

Categories

Performance Rules

Additional Information

There is no additional info for this rule.

Example Test Script
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

Analysis Results

 MessageLineColumn
1SA0054 : Parameter @date modification prior to use in a query, may negatively affect performance.128
See Also

Other Resources