Click or drag to resize

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.

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.

Categories

Design Rules, New Rules

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

 MessageLineColumn
1SA0173 : A subquery contained in the input expression of the COALESCE function. The subquery will be evaluated more than once.18
2SA0173 : A subquery contained in the input expression of the CASE function. The subquery will be evaluated more than once.58
See Also

Other Resources