[sql] 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

This question is related to sql sql-server if-statement

The answer is


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.


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

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to if-statement

How to use *ngIf else? SQL Server IF EXISTS THEN 1 ELSE 2 What is a good practice to check if an environmental variable exists or not? Using OR operator in a jquery if statement R multiple conditions in if statement Syntax for an If statement using a boolean How to have multiple conditions for one if statement in python Ifelse statement in R with multiple conditions If strings starts with in PowerShell Multiple conditions in an IF statement in Excel VBA