Multiple separate IF conditions in SQL Server

if-statement, sql, sql-server

Solution

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...

Problem

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 ```

Original source