CASE....WHEN versus IF....THEN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CASE....WHEN versus IF....THEN

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-16 : 07:01:14

Hello all

In what circumstance is CASE ...WHEN better to use than IF...THEN? In the following example, which is better to use and why? The iterates repeat for all months. (oh, what I am doing is querying a DB and inserting the calcs into another table. I am summing a number of different totals for each month. The client needs to know which values relate to which months, and therefore, I am setting the month value for each row (1 row of calcs per month). The query iterates for every month, selecting all the invoices that match the @monthcount value.)

If....else
If (@monthCount = 1)
begin
Set @month = 'January'
end
else if (@monthCount = 2)
begin
Set @month = 'February'
end.................
Insert into DBname [Month] select @month

If.....then
If (@monthCount = 1)
THEN Set @month = 'January'
else if (@monthCount = 2)
THEN Set @month = 'February'
.................
Insert into DBname [Month] select @month

Case.....when
Set @month = (CASE
WHEN datepart(month, InvoiceDate) = 1 THEN 'January'
WHEN datepart(month, InvoiceDate) = 2 THEN 'February' end.................

So which of the above solutions would be most optimal? (as it stands, the query has been running for an hour, and is not yet 1/4 the way through, so anything to reduce the timescale would help).

Thanks in advance

Hearty head pats

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-12-16 : 07:43:42

I have a feeling that the bottleneck isn't caused by this particular part of the code. Are you using a cursor to query the database?!

Anyway, in answer to your question I personally would go for the CASE statement, something like :


Set @month = (CASE datepart(month, InvoiceDate)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February' end.................

.. but I still don't think that's causing your the performance problems.

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-16 : 07:52:54

Hello again
Could you tell me why a CASE statement is best? What advantages does it offer over the other examples in this scenario?

Also, I think the bottleneck is more likely caused by the number of aggregates being performed (each based on several conditions) and by the number of rows of data being queried (several million).

Thanks again

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-16 : 07:54:00

Oh, and I am not using a cursor - I have read on this forum many a time that they are not to be used!!!

Hearty head pats

dsdeming

479 Posts

Posted - 2004-12-16 : 08:22:13

The main difference between the two is that if you use 12 nested ifs, you'll be doing 12 selects, whereas you'll only do one select with the case statement.

Try displaying the estimated execution plan for the following code in Query Analyzer and you'll see the difference.

DECLARE @a int

IF datepart( m, getdate() ) = 1
set @a = 1
ELSE IF datepart( m, getdate() ) = 2
set @a = 2
ELSE
set @a = 0

SET @a = CASE datepart( m, getdate() )
WHEN 1 THEN 1
WHEN 2 THEN 2
ELSE 0
END

Dennis

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-16 : 08:32:20

Ahhh, I see what you mean! Well, that makes perfect sense!

Thanks a lot!

Hearty head pats

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-16 : 08:50:51

Remember that while they can sometimes be used to acheive similiar results, CASE and IF are completely different:

IF is a control of flow statement; it indicates which T-SQL statement to evaluate next, based on a condition.

CASE is a function -- it simply returns a value.

- Jeff

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-12-16 : 08:51:28

quote:
Originally posted by dsdeming

The main difference between the two is that if you use 12 nested ifs, you'll be doing 12 selects, whereas you'll only do one select with the case statement.

Try displaying the estimated execution plan for the following code in Query Analyzer and you'll see the difference.

DECLARE @a int

IF datepart( m, getdate() ) = 1
set @a = 1
ELSE IF datepart( m, getdate() ) = 2
set @a = 2
ELSE
set @a = 0

SET @a = CASE datepart( m, getdate() )
WHEN 1 THEN 1
WHEN 2 THEN 2
ELSE 0
END

Dennis

True true.. and even if you forget the performance side of things, it just seems to make more sense to use a case instead of having 12 IF statements when all you're checking is the same value 12 times. But to be honest, even if you used 12 IFs I doubt the performance will improve that much.

My 2 pence

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-16 : 09:56:01

Thanks guys! Things are a little clearer in this cloudy head of mine!!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources