== Why is this an issue?

Theoretically, ``++CASE++``, ``++COALESCE++``, and ``++IIF++`` evaluate conditions sequentially, stopping with the first one that evaluates to ``++TRUE++``. In reality, these expressions evaluate aggregate functions and non-native service calls such as ``++CONTAIN++`` and ``++FREETEXT++`` first, and _then_ pass the results into the expression. That means that if you're relying on short-circuit behavior to avoid runtime errors with these arguments, you will not get it. You can work around the issue by wrapping such calls in a subselect.


=== Noncompliant code example

[source,sql]
----
SELECT 
  CASE 
    WHEN @i = 0 
      THEN 1 
    ELSE MIN(1/@i)  -- Noncompliant
  END; 
----


=== Compliant solution

[source,sql]
----
SELECT 
  CASE 
    WHEN @i = 0 
      THEN 1 
    ELSE (SELECT MIN(1/@i))
  END; 
----


ifdef::env-github,rspecator-view[]

'''
== Implementation Specification
(visible only on this page)

=== Message

Move this [aggregate function|non-native service] into a sub-select to avoid out-of-order evaluation.


=== Highlighting

aggregate function / non-native service call


'''
== Comments And Links
(visible only on this page)

=== on 19 Jul 2017, 16:43:08 Ann Campbell wrote:
https://sqlperformance.com/2014/06/t-sql-queries/dirty-secrets-of-the-case-expression

https://connect.microsoft.com/SQLServer/feedback/details/690017/case-coalesce-wont-always-evaluate-in-textual-order

https://connect.microsoft.com/SQLServer/feedback/details/691535/aggregates-dont-follow-the-semantics-of-case

https://connect.microsoft.com/SQLServer/feedback/details/780132/

https://learningintheopen.org/2013/11/22/technical-microsoft-sql-server-full-text-search-and-the-problem-with-nullempty-predicates/

endif::env-github,rspecator-view[]
