Modification of if statement with an else

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 2005 Forums

Transact-SQL (2005)

Modification of if statement with an else

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2010-06-22 : 17:03:57

HI,
I have the following code currently in a stored procedure:

if @ParmKey = 'ALL' begin
DECLARE SiteCur CURSOR FAST_FORWARD FOR
select site, app_db_name from site where site.type = 'S'
end
else begin
DECLARE SiteCur CURSOR FAST_FORWARD FOR
select site.site, site.app_db_name from site inner join site_group
on site_group.site = site.site
where site.type = 'S' and site_group.site_group = @ParmKey
end
---------------------------------------------------------------------------------------------

However now the first if statement need to be replaced with the following three scenarios:

select s.site, s.app_db_name from site s inner join site_group sg on s.site = sg.site
where sg.site_group = 'ABC'

select s.site, s.app_db_name from site s inner join site_group sg on s.site = sg.site
where sg.site_group = 'KLM'

select s.site, s.app_db_name from site s inner join site_group sg on s.site = sg.site
where sg.site_group = 'XYZ'

Thus the first part of the original statement will be modified as:

If @Parmkey = 'ABC' begin
DECLARE SiteCur CURSOR FAST_FORWARD FOR
select s.site, s.app_db_name from site s inner join site_group sg on s.site = sg.site
where sg.site_group = 'ABC'
end

Now the statement for KLM and XYZ need to be processed.

ANd the else statement should remain the same.

What would be the final code here with the changes.
I appreciate any help.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-06-22 : 19:09:57

If ...
else if ...
else if ...
else

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

gamaz
Posting Yak Master

104 Posts

Posted - 2010-06-23 : 10:06:09

Thanks Bustaz for your help. It worked perfectly. I was not sure how the else if statement works for transact sql. Regards.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-23 : 10:42:23

It doesn't. There's no such thing as the else if in SQL. What Bustaz's done is nest a whole bunch of if statements. Equivalent to

If <condition>
  <actions>
else 
  if <conditions>
    <actions>
  else 
    if <conditions>
      <actions>
    else
      <actions>

--
Gail Shaw
SQL Server MVP

gamaz
Posting Yak Master

104 Posts

Posted - 2010-06-24 : 10:54:42

OK Gail. I understand your point. So I misunderstood this as if elseif. However now I realize this is a nested if. I have applied this concept to get resolution of my isssue.
I did not realize I was doing a nested if. Thanks for correcting me. Regards.

MikeyLikesIt
Starting Member

1 Post

Posted - 2010-07-12 : 13:27:31

Isn't it true that both ways to write this code equate to the same result, since the indents and newlines seem to be ignored? In which case, why not write it the simplest way. The logic is essentially the same either way, whether it's officially called an "Else If" command or not. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources