SA0173 : COALESCE, IIF, and CASE input expressions containing sub-queries will be evaluated multiple times

The topic describes the SA0173 analysis rule.

Message

COALESCE, IIF, and CASE input expressions containing sub-queries will be evaluated multiple times

Description

The rule checks for usage of sub-queries as input expressions in COALESCE, IIF and CASE functions.

If the input expressions should not be used with sub-queries, the sub-query will be evaluated once for each option in the expression, and each evaluation could return different results depending on the isolation level.

How to fix

To ensure consistent results and better performance, move the sub-query out of the expression.

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

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
1SELECT  COALESCE( (SELECT  col1
2                   FROM  table1
3                   WHERE  col2 > 0), 1)
4
5SELECT  CASE(SELECT  COUNT(*)
6             FROM  sys.objects)
7            WHEN 1 THEN 1
8            ELSE 2
9        END
Analysis Results
  Message Line Column
1 SA0173 : A subquery contained in the input expression of the COALESCE function. The subquery will be evaluated more than once. 1 8
2 SA0173 : A subquery contained in the input expression of the CASE function. The subquery will be evaluated more than once. 5 8
See Also

Other Resources

Analysis Rules