Consider this
SELECT
CASE
WHEN 1=1 THEN 1
WHEN 1=1 THEN 1/0
END
This returns 1. A clear example of how the WHEN
s are evaluated in order.
However, when I do this:
DECLARE @VAR DATETIME2
SELECT
CASE
WHEN 1=1 THEN 1
WHEN 1=0 THEN ISNUMERIC(@VAR)
END
I get an error:
Msg 8116, Level 16, State 1, Line 9
Argument data type datetime2 is invalid for argument 1 of isnumeric function.
In the latter example, is the second WHEN
evaluated before the first one? Why would it be evaluated at all, considering the 1=0 boolean expression?
@@VERSION
:
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25
Executing a query is done in two phases (as Panagiotis Kanavos already indicated in a comment).
The compilation phase involving a syntax check and type compatibility checks.
The evaluation phase, i.e., running the compiled query.
Checking the type compatibility of @var
in ISNUMERIC(@var)
is done in phase 1. If this phase fails, phase 2 is never executed.
The calculation 1/0
would be done in phase 2. But, as you already pointed out, it is in the second WHEN which is not executed. However, the syntax and compatibility checks are performed for the whole query and, btw., the outcome of the first WHEN is not known at this time, because the expressions are not evaluated yet.
Phase 1, the compilation, can lead to compilation errors.
Phase 2, the actual execution, can lead to execution errors, sometimes also called runtime errors or exceptions (depending on the programming language or system).