If Else or Case When, Then

Dev Shed Forums Sponsor:

December 14th, 2004, 12:29 PM

rmhpirate

Contributing User

Join Date: May 2004

Location: Greenville, NC

Posts: 258

Time spent in forums: 1 Day 7 h 8 m 49 sec
Reputation Power: 7

If Else or Case When, Then

I have a query using Transact-SQL and it is moderately complex. I was wondering if someone could tell me if it would be better to use IF...ELSE statements or CASE, WHEN...THEN statements.

What I have is something like this...

Code:

SELECT 
something something 
code = CASE

WHEN something IN (A list of things)
IF something else
THEN 4

I have just though of doing IF...ELSE statements to keep it simply, but this query needs to run in a relative quick time frame. I guess my question is, is IF...ELSE faster or is it faster to use a CASE statement?

December 17th, 2004, 07:27 PM

Aforsythe

Contributing User

Join Date: Dec 2004

Posts: 104

Time spent in forums: 22 h 8 m 10 sec
Reputation Power: 4

In my experience, the difference between the two relies on readability. Also in my experience, my heavy load scripts take a while regardless of which one I use, and my light load scripts finish quickly regardless of which one I use.

Based solely on your example, I would probably go with IF...ELSE

I typically reserve the CASE statement for times when I am trying to avoid:

Code:

IF Something
   Do this
ELSE
  IF Something
     Do this
  ELSE
    IF Something
      Do this
    ELSE
       IF Something
          Do this


Etc....

December 22nd, 2004, 08:55 AM

rmhpirate

Contributing User

Join Date: May 2004

Location: Greenville, NC

Posts: 258

Time spent in forums: 1 Day 7 h 8 m 49 sec
Reputation Power: 7

Went with IF...ELSE

I decided to go with the IF...ELSE statements to allow the code to drill done all the options to obtain a solution. This is a rather large amount of code (for what is doing)....what I have is something like this.

DECLARE @Code Int(1)

SELECT
something...something...somthing

IF(something
BEGIN
IF(something
BEGIN
SELECT @Code = 1
END
END

Now there is a whole bunch of these through out the code...the only problem I'm having now is that I'm getting and error at the FROM statement after all the IF...ELSE's and SELECT statement. I can't figure out what it is.

If you know what may be causing that, it would be appreciated. All IF BEGINS are closed with END statements...

December 27th, 2004, 07:48 PM

Aforsythe

Contributing User

Join Date: Dec 2004

Posts: 104

Time spent in forums: 22 h 8 m 10 sec
Reputation Power: 4

Well, to know for sure, I would need a better example, are you trying to build a dynamic query based on the results of IF tests?

If so, you may need to look into sp_executesql @Query

and build your query by concatinating strings as you go through the if tests.

If that's not what you're trying to do, try posting your actual query with any peices that might violate an NDA replaced with fake table names etc...

December 28th, 2004, 09:03 AM

rmhpirate

Contributing User

Join Date: May 2004

Location: Greenville, NC

Posts: 258

Time spent in forums: 1 Day 7 h 8 m 49 sec
Reputation Power: 7

I guess I am trying to build a dynamic query based on the results of the IF statements. So I have something like this...(a better example)

DECLARE @Code Int, @Grade Varchar(8), @Weights Float

Code:

SELECT a.key, b.plant, c.cmd, Code = @Code, Grade = @Grade, Weights = @Weights

IF(a.key = 'R')
BEGIN
SET @Code = 1
SET @Weights = 2
END

ELSE
IF(a.key = 'K')
BEGIN
IF(b.plant = '2')
BEGIN
SET @Grade = 'TEST'
END
ELSE
SET @Weiths = 2
SET @Code = 2
END
END

FROM codetable a, planttable b, c.cmdtable

WHERE where clause data

This is a small snippet of code (the actual code is quite larger), but I hope this helps. I simple want the code to run through and assign values to the declared variables depending on the IF statements. This way the query will return the correct information depending on what data is involved.

If you need more code let me know. Thanks for your help/

December 28th, 2004, 10:04 AM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,775

Time spent in forums: 2 Weeks 4 Days 16 h 24 m 58 sec
Reputation Power: 37

This is a typical example of when you should use case expressions. (There is no case statement in T-SQL.)

Code:

select @code = 
  case when a.key = 'R' then 1 else 2 end,
@weights = 
case when a.key = 'R'  then 2 else 3 end,
@grade = 
case when a.key = 'K' and b.plant = 2 then 'TEST' else null end
from t where <condition>

December 28th, 2004, 01:13 PM

Scorpions4ever

Banned ;)

Dev Shed God 6th Plane (7500 - 7999 posts)

Join Date: Nov 2001

Location: Glendale, Los Angeles County, California, USA

Posts: 7,536

Time spent in forums: 1 Month 1 Day 6 h 51 m 7 sec
Reputation Power: 876

I usually use CASE...WHEN when I'm returning multiple rows with a SELECT statement. It is cumbersome to use IF...ELSE, because this requires you to use a cursor to iterate through the result set and a temp table to hold the results.

Code:

SELECT field1,
           field2,
           CASE WHEN field3 = 0
                THEN 'Falsimo'
                 ELSE 'True'
           END
FROM    Tablename

__________________
Up the Irons
What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
"Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
Down with Sharon Osbourne
Puzzle of the Month solved by Keath and KevinADC, superior perl programmers of the month
Looking for a perl job with kick-*** programmers in a well-known NASDAQ listed tech company with branches in the US and Europe? We're hiring. PM me for details. Requirements

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: