Stopping a SQL CASE

So it doesn't evaluate every case

[mattglet]

I have a query:

SELECT status = (CASE WHEN EXISTS (SELECT something FROM table1 WHERE something = 1) WHEN (SELECT something FROM table2 WHERE something = 2) ELSE 3 END)

Is there any way to make the CASE end if it finds the first statement to be true? In T-SQL, every CASE gets evaluated before returning, which causes a bit of unnecessary performance loss (IMO). BREAK and END don't work. Anything else?

[MozMan]

There are actually two types of CASE functions, "simple" and "searched". The "Simple" variety simply returns the first case that evalutes true and stops processing the rest. The "Searched" variety processed all cases because it returns all that evaluate true.

It looks like you are using the "searched" variety. If you use the "simple" version, you will achieve the cost savings you are looking for.

From Books Online:

Simple CASE function:

CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Searched CASE function:

CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Basically, the difference is in specifying an input expression.

HTH

-Moz

[syber]

I beg to differ. It does not matter what type of CASE statement is used - both return the value from the first expression that evaluates to TRUE.

Art

[MozMan]

both return the value from the first expression that evaluates to TRUE.

You are absolutely correct. I mis-read the documentation in Books Online. I found this in there today:

Simple CASE function:

*Evaluates input_expression, and then, in the order specified, evaluates input_expression = when_expression for each WHEN clause.

*Returns the result_expression of the first (input_expression = when_expression) that evaluates to TRUE.

*If no input_expression = when_expression evaluates to TRUE, SQL Server returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Searched CASE function:

*Evaluates, in the order specified, Boolean_expression for each WHEN clause.

*Returns result_expression of the first Boolean_expression that evaluates to TRUE.

*If no Boolean_expression evaluates to TRUE, SQL Server returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

So, it appears that, in terms of evaluating the entire case structure, that will always happen. The only real difference between the two is the type of comparison that is happening.

Thanx for straightening me out on that!

-Moz