SR0015: Extract deterministic function calls from WHERE predicates

A WHERE predicate contains one or more deterministic function calls.

In a WHERE predicate, a function call is deterministic if its value does not depend on the selected data. Such calls could cause unnecessary table scans, which decrease database performance.

To resolve this issue, you can assign the result of the call to a variable that you use in the WHERE predicate.

You might suppress this warning if the table or tables that the WHERE predicate references will never contain more than a few rows.

In the first example, the stored procedure includes a deterministic function call, ABS(@param1), in the WHERE predicate. In the second example, a temporary variable holds the result of the call.

CREATE PROCEDURE [dbo].[Procedure2WithWarning]
@param1 INT = 0, 
AS
BEGIN
SELECT [c1], [c2], [c3], [SmallString] 
FROM [dbo].[Table1]
WHERE [c2] > ABS(@param1)
END

CREATE PROCEDURE [dbo].[Procedure2Fixed]
@param1 INT = 0, 
AS
BEGIN
DECLARE @AbsOfParam1 INT
SET @AbsOfParam1 = ABS(@param1)

SELECT [c1], [c2], [c3], [SmallString] 
FROM [dbo].[Table1]
WHERE [c2] > @AbsOfParam1
END

Concepts