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

User · Answer

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

User · Answer

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

User · 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      -- lt --- 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

[sql] Multiple separate IF conditions in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to if-statement