SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates |
![]() |
The topic describes the SA0174 analysis rule.

The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates

The rule checks usage of aggregate functions or full text search predicates inside CASE expression.
The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied.
In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible.
Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement.
For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. This occurs prior to evaluating the CASE expression.
WITH Data (value) AS
(
SELECT 0
UNION ALL
SELECT 1
)
SELECT
CASE
WHEN MIN(value) <= 0 THEN 0
WHEN MAX(1/value) >= 100 THEN 1
END
FROM Data ;
You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions.

Review the CASE expression and suppress the rule after considering the unreliable short-circuit behavior of aggregate functions and full text search predicates.

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.


Design Rules, Bugs

Dirty Secrets of the CASE Expression
SQL Server – Full Text Search and the problem with null/empty predicates
Don’t depend on expression short circuiting in T-SQL (not even with CASE)

SQL
1declare @v int = 0 2SELECT 3 CASE 4 WHEN @v = 0 5 THEN 1 6 ELSE (SELECT MIN(1/@v)) 7 END; 8 9SELECT 10 CASE 11 WHEN @v = 0 12 THEN 1 13 ELSE MAX(1/@v) 14 END; 15 16DECLARE @SearchWord nvarchar(30) = '' 17SELECT Description 18FROM Production.ProductDescription 19WHERE 20 case 21 when @SearchWord = '' or @SearchWord is null then 1 22 when 1= (select top (1) 1 from Production.ProductDescription where FREETEXT(Description, @SearchWord) ) then 1 23 when 1= (select top (1) 1 from Production.ProductDescription where contains(Description, @SearchWord) ) then 1 24 when FREETEXT(Description, @SearchWord) then 1 25 when contains(Description, @SearchWord) then 1 26 when (@SearchWord is not null and contains(Description,@SearchWord)) then 1 27 when (@SearchWord is not null and freetext(Description,@SearchWord)) then 1 28 29 else 0 end = 1 30 31 32;WITH Data (value) AS 33( 34SELECT 0 35UNION ALL 36SELECT 1 37) 38SELECT 39 CASE 40 WHEN MIN(value) <= 0 THEN 0 41 WHEN MAX(1/value) >= 100 THEN 1 42 END |

Message | Line | Column | |
---|---|---|---|
1 | SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. | 14 | 9 |
2 | SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. | 25 | 10 |
3 | SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. | 26 | 8 |
4 | SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. | 27 | 36 |
5 | SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. | 28 | 38 |
6 | SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. | 42 | 11 |
