SQL Server If Else If statement not working

Question

I have the following stored procedure that will update, insert or delete. My problem with that the DELETE section is never entered. If I only use the delete section is works fine but adding an 'IF' block before it and the code is never triggered. This is SQL Server 2005. The stored procedure code:

ALTER PROCEDURE phyadmSetNumbers
    @OfficeID int,
    @Number varchar(50),
    @NumberTypeID int
AS
BEGIN
    DECLARE @NumberID int

    EXEC phyadmGetNumberID @OfficeID, @NumberTypeID, @NumberID output
    IF ((LEN(@Number) > 0) AND (@@RowCount = 1)) 
    BEGIN           
        UPDATE Number
        SET Number = @Number, LastUpdate = GetDate()
        WHERE ID = @NumberID        
    END
    ELSE IF ((LEN(@Number) > 0) AND (@@RowCount = 0))  
    BEGIN
        EXEC phyadmInsNumber @OfficeID, @Number, @NumberTypeID  
    END
    ELSE IF (((LEN(@Number) = 0) OR @Number IS NULL) AND (@@RowCount = 1))
    BEGIN       
        SELECT 'In delete if statement'
        SELECT 'NumberID is ' + @NumberID
        DELETE FROM Number
        WHERE ID = @NumberID
        select * from Number where ID = @NumberID

        DELETE FROM OfficeNumber
        WHERE OfficeID = @OfficeID AND NumberID = @NumberID 

        select * from OfficeNumber WHERE OfficeID = @OfficeID AND NumberID = @NumberID  
    END
END

If I comment out the top 2 'IF' statments and only run the last, it works fine. As shown below: THIS WORKS FINE if it is the only 'IF' block.

IF (((LEN(@Number) = 0) OR @Number IS NULL) AND (@@RowCount = 1))
        BEGIN       
            SELECT 'In delete if statement'
            SELECT 'NumberID is ' + @NumberID
            DELETE FROM Number
            WHERE ID = @NumberID
            select * from Number where ID = @NumberID

            DELETE FROM OfficeNumber
            WHERE OfficeID = @OfficeID AND NumberID = @NumberID 

            select * from OfficeNumber WHERE OfficeID = @OfficeID AND NumberID = @NumberID

How should if...else if be used in SQL? What is wrong with the above code? Thanks.

UPDATE Taking the suggestions..this is my new code:

DECLARE @NumberID int

EXEC phyadmGetNumberID @OfficeID, @NumberTypeID, @NumberID output
IF (@@RowCount = 1)
BEGIN
    IF (LEN(@Number) > 0)
    BEGIN
        UPDATE Number
        SET Number = @Number, LastUpdate = GetDate()
        WHERE ID = @NumberID        
    END
    ELSE
    BEGIN
        DELETE FROM Number
        WHERE ID = @NumberID

        DELETE FROM OfficeNumber
        WHERE OfficeID = @OfficeID AND NumberID = @NumberID 
    END
END
ELSE
BEGIN
    EXEC phyadmInsNumber @OfficeID, @Number, @NumberTypeID
END

Try using just if instead of else if. If you need the else, try rewriting the else if statements as else begin if (condition) begin (action) end end — user4570983
– user4570983, Commented Jul 16, 2015 at 15:50
I think ELSE IF is a problem here since it is kind of tricky when you are using it. Try making a separate statement IF (LEN(@Number) = 0) AND (@@RowCount = 1) another IF (@Number IS NULL) AND (@@RowCount = 1) — Maverick
– Maverick, Commented Jul 16, 2015 at 15:53

Ejesalva · Accepted Answer · 2015-07-16 15:57:49Z

4

If (1=0)
BEGIN
    SELECT [col] FROM [table]
END
ELSE 
BEGIN
    IF (1=1)
    BEGIN
        SELECT * FROM [table]
    END
END

Your going to want to separate them out and nest your IF ... ELSE statements.

answered Jul 16, 2015 at 15:57

Ejesalva

12311 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gloria Santin Over a year ago

Thanks. That worked. Above is my final code...in case anybody else has this problem.

Dane · Accepted Answer · 2015-07-16 16:01:31Z

The problem here is that the conditions are being met before falling into the else if. As stated by Ejesalva you need to separate them out, so I think the last statement should just be else:

ALTER PROCEDURE phyadmSetNumbers
@OfficeID int,
@Number varchar(50),
@NumberTypeID int
AS
BEGIN
DECLARE @NumberID int

EXEC phyadmGetNumberID @OfficeID, @NumberTypeID, @NumberID output
IF ((LEN(@Number) > 0) AND (@@RowCount = 1)) 
BEGIN           
    UPDATE Number
    SET Number = @Number, LastUpdate = GetDate()
    WHERE ID = @NumberID        
END
ELSE IF ((LEN(@Number) > 0) AND (@@RowCount = 0))  
BEGIN
    EXEC phyadmInsNumber @OfficeID, @Number, @NumberTypeID  
END
ELSE
BEGIN       
    SELECT 'In delete if statement'
    SELECT 'NumberID is ' + @NumberID
    DELETE FROM Number
    WHERE ID = @NumberID
    select * from Number where ID = @NumberID

    DELETE FROM OfficeNumber
    WHERE OfficeID = @OfficeID AND NumberID = @NumberID 

    select * from OfficeNumber WHERE OfficeID = @OfficeID AND NumberID = @NumberID  
END
END

I hope that makes sense

Collectives™ on Stack Overflow

SQL Server If Else If statement not working

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related