How to combine several IFs - 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

SQL Server Development (2000)

How to combine several IFs

Author

Topic

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2009-06-25 : 03:14:04

Hi all,

ok, I've learned in Jeffs Article at [url]http://weblogs.sqlteam.com/jeffs/archive/2007/05/03/60195.aspx[/url] that 'CASE' is an expression, not a statement. Therefore something like this does NOT work:

CASE @Var
   WHEN 'ABC' THEN
      BEGIN
         ....
      END
   WHEN 'DEF' THEN
      BEGIN 
         ....
      END
   END

But what is the best way to write in a stored procedure if I have e.g. 10 conditions on 1 single variable. Currently I only know:


IF @Var = 'ABC'
   BEGIN
      ...
   END
ELSE
   BEGIN
      IF @Var = 'DEF'
         BEGIN
            ...
         END
      ELSE
         BEGIN
            IF @Var = 'GHI'
               BEGIN
                  ...
               END
         END
   END

Is there any better way? A SELECT CASE would be best (for overview), but as it does not work, is there anything else?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 03:40:59

Maybe easier to read?

IF @Var = 'ABC'
	BEGIN
		...
	END
ELSE IF @Var = 'DEF'
	BEGIN
		...
	END
ELSE IF @Var = 'GHI'
	BEGIN
		...
	END

E 12°55'05.63"
N 56°04'39.26"

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2009-06-25 : 05:10:27

Definetely! I searched for 'ElseIf', and I was not aware that no additionally BEGIN is required immediately after the 'ELSE'. Thanks Peso!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 05:21:17

BEGIN and END are only needed if there are multiple statements in the IF case.
And since, in this scenario, there is only one statement (a nested IF) the additional BEGIN and END can be removed.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources