Multiple separate IF conditions in SQL Server

217,153

Solution 1

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

Solution 2

To avoid syntax errors, be sure to always put BEGIN and END after an IF clause, eg:

IF (@A!= @SA)
   BEGIN
   --do stuff
   END
IF (@C!= @SC)
   BEGIN
   --do stuff
   END

... and so on. This should work as expected. Imagine BEGIN and END keyword as the opening and closing bracket, respectively.

Solution 3

Maybe this is a bit redundant, but no one appeared to have mentioned this as a solution.

As a beginner in SQL I find that when using a BEGIN and END SSMS usually adds a squiggly line with incorrect syntax near 'END' to END, simply because there's no content in between yet. If you're just setting up BEGIN and END to get started and add the actual query later, then simply add a bogus PRINT statement so SSMS stops bothering you.

For example:

IF (1=1)
BEGIN
  PRINT 'BOGUS'
END

The following will indeed set you on the wrong track, thinking you made a syntax error which in this case just means you still need to add content in between BEGIN and END:

IF (1=1)
BEGIN
END
Share:
217,153

Related videos on Youtube

Jack Thor
Author by

Jack Thor

Updated on March 03, 2020

Comments

  • Jack Thor
    Jack Thor over 4 years

    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
    
  • Jack Thor
    Jack Thor over 10 years
    I did check the last code block that I posted. It throws an Error.
  • LittleSweetSeas
    LittleSweetSeas over 10 years
    Please poste your full statement. Error is elsewhere
  • Jack Thor
    Jack Thor over 10 years
    As stated I am already using BEGIN..END, but using that actually throws an error.
  • M.Ali
    M.Ali over 10 years
    @JackThor using BEGIN..END throws an error ?? it cant be that its something else throwing error. To debug it I would take out all of the code and use PRINT statement in each BEGIN END block something like PRINT 'Block 1' etc etc to check exactly between which blocks your code control is jumping in and out.
  • Jack Thor
    Jack Thor over 10 years
    Using the Begin..END gives it a red squiggly underline for the last End in the group of If statement that I inserted. And when I hover over it it says Incorrect syntax near "END". Expecting "CONVERSATION"
  • M.Ali
    M.Ali over 10 years
    @JackThor see my update and you are missing some BEGIN END blocks in you MOST OUTER ELSE IF BLOCK I have added them in comments starting with two -- , ignore the commented ELSE and IF ELSE :)