Geek Answers Handbook

Does the CASE expression invoke 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 straightforward - I'm testing a split name request. Therefore, I am testing a scenario where the name has no spaces, and I get the following exception:

Invalid length parameter passed to SUBSTRING function.

Why? Should he not evaluate the first sentence and immediately move on to the ELSE ? How can I get around this ??

+5
source share
2 answers

The optimizer is smart enough to notice that you have a constant expression and try to evaluate it. Passing a constant through a variable would fool it:

 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, the processor would evaluate the THEN expression only if WHEN is true, otherwise it would only evaluate the ELSE expression. But even before Optimizer tries to replace all constant expressions with calculated values, so that the processor does not have to re-read them for each line. It was called Constant Folding . "

+11
source

Using variables 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 problem with your code is that it checks that each part will work when passing a static value. I do not like that CHARINDEX(' ', 'Sara') - 1 allowed equal to -1. A hack to get around this would be to wrap this function in an 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
+7
source

More articles:


All Articles