Jack Thor Jack Thor - 4 months ago 15
SQL Question

Multiple separate IF conditions in SQL Server

I have multiple

IF
statements that are independent of each other in my stored procedure. But for some reason they are being nested inside each other as if they are part of one big if statement

ELSE IF(SOMETHNGZ)
BEGIN
IF(SOMETHINGY)
BEGIN..END
ELSE IF (SOMETHINGY)
BEGIN..END
ELSE
BEGIN..END
--The above works I then insert this below and these if statement become nested----
IF(@A!= @SA)

IF(@S!= @SS)

IF(@C!= @SC)

IF(@W!= @SW)
--Inserted if statement stop here
END
ELSE <-- final else


So it will be treated like this

IF(@A!= @SA){
IF(@S!= @SS){
IF(@C!= @SC) {
IF(@W!= @SW){}
}
}
}


What I expect is this

IF(@A!= @SA){}
IF(@S!= @SS){}
IF(@C!= @SC){}
IF(@W!= @SW){}


I have also tried this and it throws
Incorrect syntax near "ELSE". Expecting "CONVERSATION"


IF(@A!= @SA)
BEGIN..END
IF(@S!= @SS)
BEGIN..END
IF(@C!= @SC)
BEGIN..END
IF(@W!= @SW)
BEGIN..END


Note that from
ELSE <--final else
down is now nested inside
IF(@W!= @SW)
Even though it is part of the outer if statement
ELSE IF(SOMETHNGZ)
before.

EDIT

As per request my full statement

ALTER Procedure [dbo].[SP_PLaces]
@ID int,
..more params
AS
BEGIN
SET NOCOUNT ON
DECLARE @SomeId INT
..more varaible
SET @SomeId = user define function()
..more SETS
IF(@ID IS NULL)
BEGIN
BEGIN TRY
INSERT INTO Places
VAlUES(..Values...)
... more stuff...
BEGIN TRY
exec Store procedure
@FIELD = 15, ... more params...
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
RETURN 0
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
END
ELSE IF(@ID IS NOT NULL AND @ID in (SELECT ID FROM Places))
BEGIN
SELECT @MyName = Name ...
...Some stuff....
IF(SOMETHNG_1)
BEGIN TRY
UPDATE ....
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
ELSE IF(SOMETHNG_2)
BEGIN TRY
UPDATE ...
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
ELSE
BEGIN
BEGIN TRY
UPDATE ...
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
END
--The above works I then insert this below and these if statement become nested----
IF(@A!= @SA)
BEGIN
exec Stored procedure
@FIELD = 15,
... more params...
END
IF(@S!= @SS)
BEGIN
exec Stored procedure
@FIELD = 10,
... more params...
END
IF(@C!= @SC)
BEGIN
exec Stored procedure
@FIELD = 17,
... more params...
END
IF(@W!= @SW)
BEGIN
exec Stored procedure
@FIELD = 12,
... more params...
END
--Inserted if statement stop here
END
ELSE
BEGIN
SET @ResultMessage = 'Update/Delete Failed. No record found with ID:'+CONVERT(varchar(50), @ID)
SELECT @ResultMessage AS 'Message'
RETURN -1
END
Set NOCOUNT OFF
END

Answer

IF you are checking one variable against multiple condition then you would use something like this Here the block of code where the condition is true will be executed and other blocks will be ignored.

    IF(@Var1 Condition1)
     BEGIN
      /*Your Code Goes here*/
     END

ELSE IF(@Var1 Condition2)
      BEGIN
        /*Your Code Goes here*/ 
      END 

    ELSE      --<--- Default Task if none of the above is true
     BEGIN
       /*Your Code Goes here*/
     END

If you are checking conditions against multiple variables then you would have to go for multiple IF Statements, Each block of code will be executed independently from other blocks.

IF(@Var1 Condition1)
 BEGIN
   /*Your Code Goes here*/
 END


IF(@Var2 Condition1)
 BEGIN
   /*Your Code Goes here*/
 END


IF(@Var3 Condition1)
 BEGIN
   /*Your Code Goes here*/
 END

After every IF statement if there are more than one statement being executed you MUST put them in BEGIN..END Block. Anyway it is always best practice to use BEGIN..END blocks

Update

Found something in your code some BEGIN END you are missing

ELSE IF(@ID IS NOT NULL AND @ID in (SELECT ID FROM Places))   -- Outer Most Block ELSE IF
BEGIN   
     SELECT @MyName = Name ...  
    ...Some stuff....                       
    IF(SOMETHNG_1)         -- IF
                 --BEGIN
        BEGIN TRY               
            UPDATE ....                                                                 
        END TRY

        BEGIN CATCH
            SELECT ERROR_MESSAGE() AS 'Message' 
            RETURN -1
        END CATCH
                -- END
    ELSE IF(SOMETHNG_2)    -- ELSE IF
                 -- BEGIN
        BEGIN TRY
            UPDATE ...                                                      
        END TRY
        BEGIN CATCH
            SELECT ERROR_MESSAGE() AS 'Message' 
            RETURN -1
        END CATCH   
               -- END
    ELSE                  -- ELSE
        BEGIN
            BEGIN TRY
                UPDATE ...                                                              
            END TRY
            BEGIN CATCH
                SELECT ERROR_MESSAGE() AS 'Message' 
                RETURN -1
            END CATCH   
         END             
      --The above works I then insert this below and these if statement become nested----
          IF(@A!= @SA)
            BEGIN
             exec Store procedure 
                    @FIELD = 15,
                    ... more params...
            END                 
        IF(@S!= @SS)
          BEGIN
             exec Store procedure 
                    @FIELD = 10,
                    ... more params...