IF versus CASE statements

Question

IF versus CASE statements

corder

Ten Centuries

Points: 1275
More actions
November 15, 2011 at 12:22 pm

#390866

Does anyone have thoughts on the differences between these statements?
DECLARE @Result varchar(10) = ''
IF 1 > 0 BEGIN Select @Result = 'True' END
SELECT @Result
Versus
DECLARE @Result varchar(10) = ''
SELECT @Result = CASE WHEN 1 > 0 THEN 'True' ELSE '' END
SELECT @Result
Versus
DECLARE @Result varchar(10) = CASE WHEN 1 > 0 THEN 'True' ELSE '' END
SELECT @Result
I know variable defaults assignments will only work in SQL Server 2008 and higher but what about any performance differences in these. Of course it's a simple example but just wondering if one way is better than another.
Thanks,
Cliff

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

corder (11/15/2011)
Does anyone have thoughts on the differences between these statements?
DECLARE @Result varchar(10) = ''
IF 1 > 0 BEGIN Select @Result = 'True' END
SELECT @Result
Versus
DECLARE @Result varchar(10) = ''
SELECT @Result = CASE WHEN 1 > 0 THEN 'True' ELSE '' END
SELECT @Result
Versus
DECLARE @Result varchar(10) = CASE WHEN 1 > 0 THEN 'True' ELSE '' END
SELECT @Result
I know variable defaults assignments will only work in SQL Server 2008 and higher but what about any performance differences in these. Of course it's a simple example but just wondering if one way is better than another.
Thanks,
Cliff

It is not about which one is "better". IF and Case are just completely different. If statements are used to control flow of steps in a batch and a case statement determines which value to use in a column of a select statement.

Notice in your first example you may not get a result if the condition is false. In your second example you will always get a result but the contents of it are conditional.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

IF is typically used for logic flow, where CASE is used to return data values;

your example is bending an IF into a situation similar to a CASE, but an IF statement can do much more than that.

It's not like in a programming language, where you can really use them interchangably...in SQL, a CASE statemetn is much more limited.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

corder Ten Centuries Points: 1275 More actions · Answer 3

Thanks for the thoughts. Basically, I was reviewing some code and came across the IF implementation and thought why not just use a CASE statement.

drew.allen SSC Guru Points: 76984 More actions · Answer 4

Lowell (11/15/2011)
your example is bending an IF into a situation similar to a CASE, but an IF statement can do much more than that.

I would argue that he's doing the reverse, bending a CASE into a situation similar to an IF. He's testing a single static condition and the ELSE clause in the CASE does an unnecessary update. If the condition depended on some column, then the CASE would be called for, but in this case, IF is probably correct.

Of course, the situation is highly contrived. It's possible that the actual code might be better designed using a CASE statement.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

It helps to remember that CASE is an expression, not a statement.

CASE is basically like a function with "if logic" built in, a more powerful version of ISNULL() or COALESCE().

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills