Nest When Statements - SQL Server Forums

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

Transact-SQL (2000)

Nest When Statements

Author

Topic

rmhpirate
Starting Member

10 Posts

Posted - 2004-12-28 : 15:44:23

I have a large amount of code for a sp. I'm having problems with nesting the CASE WHEN statements. I have something like the following:

Code = CASE
WHEN <something>
   THEN CASE WHEN <something>
       THEN CASE WHEN <something>
            THEN 1
       ELSE
            2
       END

WHEN <something>
     THEN CASE WHEN <something>
          THEN 3

END,

Grade = CASE

I thought that the END statement was to simply close the CASE statement. The reason I say this is that when attempting to compile I get an error on the last END (that closes the CASE statment). What would be causing this issue. Is it the END statment after the ELSE? Or am I doing something else wrong?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-28 : 15:47:30

You cannot nest a case statement like this.

rmhpirate
Starting Member

10 Posts

Posted - 2004-12-28 : 15:48:47

Is there a way to nest them?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 15:52:08

There's nothing wrong with what you have as long as <something> returns a boolean and I think you are missing an end or two.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmhpirate
Starting Member

10 Posts

Posted - 2004-12-28 : 15:58:44

Should I have an END for every WHEN statement and nested when statement i.e.

Code = CASE

WHEN <something> THEN 1 END

WHEN <something>
THEN CASE WHEN <something>
THEN CASE WHEN <something>
THEN 1
ELSE
2
END
END
END

<Other nested when statements>
END,

Grade = CASE

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-28 : 16:18:22

Oops - I guess you can nest CASE statements. Though would'nt it be more readable to handle these conditions with AND's and OR's in an un-nested case statement.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 16:21:23

You need an end for each case statement.


Code = 
     CASE WHEN <something> 
             THEN 1
          WHEN <something>
             THEN CASE WHEN <something>
                          THEN CASE WHEN <something>
                                       THEN 1
                                       ELSE 2
                               END
                  END
      END

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmhpirate
Starting Member

10 Posts

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

Good deal...I'll go throught this massive amount of code and let you know how it turns out. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources