Multiple separate IF conditions in SQL Server

Question

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