SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates |
![]() |
Using aggregate functions or full-text search predicates within CASE expressions can lead to unexpected behavior and inefficient query execution.

In T-SQL code and SQL Server, a common problem arises when aggregate functions or full-text search predicates are used within CASE expressions. The CASE statement processes conditions sequentially and may encounter issues if aggregates are prematurely evaluated before the CASE logic is applied.
For example:
1WITH Data (value) AS 2( 3SELECT 0 4UNION ALL 5SELECT 1 6) 7SELECT 8 CASE 9 WHEN MIN(value) <= 0 THEN 0 10 WHEN MAX(1/value) >= 100 THEN 1 11 END 12FROM Data;
This query can lead to a divide-by-zero error during evaluation of the MAX function, before the CASE expression fully executes.
-
Early evaluation of aggregate functions can trigger errors that disrupt intended query logic.
-
Such preemptive evaluations can lead to unintended exceptions, like divide-by-zero, due to input values not being processed as expected.

Address issues caused by using aggregate functions or full-text search predicates inside CASE expressions to prevent unexpected errors.
Follow these steps to address the issue:
-
Review the CASE expression to ensure that aggregate functions are not evaluated prematurely. Consider using alternative logic structures if necessary.
-
Avoid using aggregate functions directly in CASE conditions. Instead, pre-compute necessary values in a Common Table Expression (CTE) or subquery.
-
Simplify conditions to prevent errors such as divide-by-zero by isolating them from potential problematic inputs.
For example:
1WITH PreComputedValues (minValue, maxValue, safeDivision) AS 2( 3 SELECT MIN(value), MAX(1.0 / NULLIF(value, 0)), 100 AS safeDivision 4 FROM Data 5) 6SELECT 7 CASE 8 WHEN minValue <= 0 THEN 0 9 WHEN maxValue >= safeDivision THEN 1 10 END 11FROM PreComputedValues;

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 AS INT = 0 2 3SELECT CASE 4 WHEN @v = 0 THEN 1 5 ELSE( SELECT MIN( 1 / @v ) ) 6 END; 7 8SELECT CASE 9 WHEN @v = 0 THEN 1 10 ELSE MAX( 1 / @v ) 11 END; 12 13DECLARE @SearchWord AS NVARCHAR( 30 ) = '' 14 15SELECT Description 16FROM Production.ProductDescription 17WHERE CASE 18 WHEN @SearchWord = '' OR 19 @SearchWord IS NULL THEN 1 20 WHEN 1 = ( SELECT TOP( 1 ) 21 1 22 FROM Production.ProductDescription 23 WHERE FREETEXT( Description, @SearchWord ) ) THEN 1 24 WHEN 1 = ( SELECT TOP( 1 ) 25 1 26 FROM Production.ProductDescription 27 WHERE CONTAINS( Description, @SearchWord ) ) THEN 1 28 WHEN FREETEXT( Description, @SearchWord ) THEN 1 29 WHEN CONTAINS( Description, @SearchWord ) THEN 1 30 WHEN( @SearchWord IS NOT NULL AND 31 CONTAINS( Description, @SearchWord ) ) THEN 1 32 WHEN( @SearchWord IS NOT NULL AND 33 FREETEXT( Description, @SearchWord ) ) THEN 1 34 ELSE 0 35 END = 1; 36 37WITH Data( value ) AS ( SELECT 0 38 UNION ALL 39 SELECT 1 ) 40SELECT CASE 41 WHEN MIN( value ) <= 0 THEN 0 42 WHEN MAX( 1 / value ) >= 100 THEN 1 43 END |

Message | Line | Column | |
---|---|---|---|
1 | SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. | 10 | 17 |
2 | SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. | 28 | 16 |
3 | SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. | 29 | 16 |
4 | SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. | 31 | 17 |
5 | SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. | 33 | 17 |
6 | SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. | 42 | 17 |
