Does CASE expression evaluate all cases before processing?

Question

I have the following query:

SELECT
CASE 
    WHEN 'Sara' like '% %' THEN SUBSTRING('Sara', 1, CHARINDEX(' ', 'Sara') - 1)
    ELSE 'Sara'
END AS FirstName,
CASE 
    WHEN 'Sara' like '% %' THEN SUBSTRING('Sara', CHARINDEX(' ', 'Sara') + 1, 8000)
    ELSE ''
END AS LastName

Pretty straight-forward - I'm testing a name split query. So I test the scenario where the name has no spaces and I get the following exception:

Invalid length parameter passed to the SUBSTRING function.

Why is that? Should it not evaluate the first clause and immediate go to the ELSE? How do I get around this..?

Answer 1

Optimizer is smart enough to notice you have constant expression there and try to evaluate it. Passing the constant through a variable would trick it to run:

DECLARE @TestString nvarchar(100) = 'Sara';

SELECT
    CASE 
        WHEN @TestString like '% %'
        THEN SUBSTRING(@TestString, 1, CHARINDEX(' ', @TestString) - 1)
        ELSE @TestString
    END AS FirstName,
    CASE 
        WHEN @TestString like '% %'
        THEN SUBSTRING(@TestString, CHARINDEX(' ', @TestString) + 1, 8000)
        ELSE ''
    END AS LastName

To answer the question, Processor would calculate THEN expression only if WHEN is true, otherwise it will calculate only ELSE expression. But even before that Optimizer would try to substitute all constant expressions with calculated values so that Processor won't have to re-calculate them for each row. It's called "Constant Folding".

Answer 2

Using a variable works;

DECLARE @NameString varchar(10); SET @NameString = 'Sara'

SELECT
CASE 
    WHEN @NameString like '% %' THEN SUBSTRING(@NameString, 1, CHARINDEX(' ', @NameString) - 1)
    ELSE @NameString
END AS FirstName,
CASE 
    WHEN @NameString like '% %' THEN SUBSTRING(@NameString, CHARINDEX(' ', @NameString) + 1, 8000)
    ELSE ''
END AS LastName

The issue with your code is that it checks that each part will work when a static value is passed. It doesn't like that CHARINDEX(' ', 'Sara') - 1 resolves to equal -1. A hack to get around this would be to wrap this function in the ABS() function;

SELECT
CASE 
    WHEN 'Sara' like '% %' THEN SUBSTRING('Sara', 1, ABS(CHARINDEX(' ', 'Sara') - 1))
    ELSE 'Sara'
END AS FirstName,
CASE 
    WHEN 'Sara' like '% %' THEN SUBSTRING('Sara', CHARINDEX(' ', 'Sara') + 1, 8000)
    ELSE ''
END AS LastName