sqlsql-servercase

Is checking the requirements for ISNUMERIC() happening before evaluation of the CASE WHEN logic


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 WHENs 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


Solution

  • Executing a query is done in two phases (as Panagiotis Kanavos already indicated in a comment).

    1. The compilation phase involving a syntax check and type compatibility checks.

    2. 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).