user2124871 user2124871 - 5 months ago 7
SQL Question

Does CASE expression evaluate all cases before processing?

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

Optimizer is smart enough to notice you have constant expression there and try to evaluate it. Try this:

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 and ELSE expressions only if WHEN is True, 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".