If Else If condition failing

28 Dec 2005 7:18 PM

tsigler

This is only the second stored procedure that I've written, and I'm
having some issues with a conditional statement that I can't figure
out. The statement has a conditional If statement with two Else If's
that checks for a passed parameter's value (an integer I pass when
executing the SP via ASP). Here is basically what I have:

-- @SET STATUS has 3 valid values:
-- 1 - Don't set status
-- 2 - Set inactive
-- 3 - Set active
CREATE PROCEDURE dbo.sp_SomeProcedure
@SET_STATUS int

AS

SET NOCOUNT ON
IF @SET_STATUS = 1
    IF EXISTS (SELECT STATEMENT)
        BEGIN
            UPDATE STATEMENT
        END
    ELSE
        BEGIN
            INSERT STATEMENT
        END

ELSE IF @SET_STATUS = 2
    IF EXISTS (SELECT STATEMENT)
            BEGIN
                UPDATE STATEMENT
                                                END
ELSE IF @SET_STATUS = 3
    IF EXISTS (SELECT STATEMENT)
            BEGIN
                UPDATE STATEMENT
                                                END
GO

When @SET_STATUS is set to either 1 or 2, the sequel statements run
fine and the corresponding row gets updated or inserted accordingly.
If however the @SET_STATUS is passed as 3, the procedure executes fine,
but the update statement is not run.

I've manually plugged in the "exists" condition and the update
statement for this part of the procedure and they fire off correctly
when run in QA. I'm at a loss. I've pretty much determined that it is
failing at the "ELSE IF @SET_STATUS = 3" condition, but I don't know
why since it passes the syntax check.

Am I missing something here? Thanks in advance!

28 Dec 2005 7:29 PM

Aaron Bertrand [SQL Server MVP]

Here's how I recommend the structure:

IF @SET_STATUS = 1
BEGIN
    ... do stuff ...
END
IF @SET_STATUS = 2
BEGIN
    ... do stuff ...
END
IF @SET_STATUS = 3
BEGIN
    ... do stuff ...
END

There's no need for ELSE, and you should always wrap the result of an IF
statement in BEGIN/END.

<tsig***@gmail.com> wrote in message
Show quote

news:1135797481.201874.264140@g47g2000cwa.googlegroups.com...
> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
>
> -- @SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @SET_STATUS int
>
> AS
>
> SET NOCOUNT ON
> IF @SET_STATUS = 1
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE
> BEGIN
> INSERT STATEMENT
> END
>
> ELSE IF @SET_STATUS = 2
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE IF @SET_STATUS = 3
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> GO
>
>
>
> When @SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
>
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
>
> Am I missing something here? Thanks in advance!
>

28 Dec 2005 7:37 PM

Tomo

Aaron, you're a rock star! It must not have liked the second "else if"
and after updating like you suggested, everything works like a champ.
Thanks!!!

28 Dec 2005 7:38 PM

Trey Walpole

The ELSE IF @SET_STATUS = 3 is the else of the IF EXISTS() under
@SET_STATUS = 2
So it's never actually getting to the IF @SET_STATUS = 3 statement.
Add BEGIN..END around the IF EXISTS() under each ELSE IF

e.g.

ELSE IF @SET_STATUS = 2
   BEGIN
     IF EXISTS (SELECT STATEMENT)
       BEGIN
         UPDATE STATEMENT
       END
   END
ELSE IF @SET_STATUS = 3
   BEGIN
     IF EXISTS (SELECT STATEMENT)
       BEGIN
         UPDATE STATEMENT
       END
   END

tsig***@gmail.com wrote:
Show quote

> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
>
> -- @SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @SET_STATUS int
>
> AS
>
> SET NOCOUNT ON
> IF @SET_STATUS = 1
>     IF EXISTS (SELECT STATEMENT)
>         BEGIN
>             UPDATE STATEMENT
>         END
>     ELSE
>         BEGIN
>             INSERT STATEMENT
>         END
>
> ELSE IF @SET_STATUS = 2
>     IF EXISTS (SELECT STATEMENT)
>             BEGIN
>                 UPDATE STATEMENT
>                                                 END
> ELSE IF @SET_STATUS = 3
>     IF EXISTS (SELECT STATEMENT)
>             BEGIN
>                 UPDATE STATEMENT
>                                                 END
> GO
>
>
>
> When @SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
>
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
>
> Am I missing something here? Thanks in advance!
>

28 Dec 2005 7:38 PM

Tomo

Thanks Trey ;)

28 Dec 2005 7:46 PM

Raymond D'Anjou

Depending on what you're doing in the 3 updates, you may be able to combine
these into 1 statement.

> IF @SET_STATUS = 1 AND NOT EXISTS (SELECT STATEMENT)
> BEGIN
> INSERT STATEMENT
> END
ELSE
> BEGIN
> UPDATE STATEMENT
> END

Explain what the 3 updates do in your procedure and maybe we can suggest a
better way.

In this part:

> ELSE IF @SET_STATUS = 2 (or 3)
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END

I don't think that you need to check IF EXISTS unless you are performing an
action if this is FALSE.
Your update statement should affect 0 rows if it doesn't exist.

<tsig***@gmail.com> wrote in message
Show quote

news:1135797481.201874.264140@g47g2000cwa.googlegroups.com...
> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
>
> -- @SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @SET_STATUS int
>
> AS
>
> SET NOCOUNT ON
> IF @SET_STATUS = 1
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE
> BEGIN
> INSERT STATEMENT
> END
>
> ELSE IF @SET_STATUS = 2
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE IF @SET_STATUS = 3
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> GO
>
>
>
> When @SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
>
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
>
> Am I missing something here? Thanks in advance!
>