CODEWITHSUNDEEP
sql-serverif-statement
Gloria Santin
Gloria Santin

Reputation: 2136

SQL Server If Else If statement not working

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

Upvotes: 1

Views: 12913

Answers (2)

Dane
Dane

Reputation: 287

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

Upvotes: 0

Ejesalva
Ejesalva

Reputation: 123

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.

Upvotes: 4

Related Questions