locked
If Not exists not working properly while adding a new column to existing table. RRS feed

  • Question

  • Hi All,

    Please clarify me or tell me the root cause why If Not exists behaving differently while executing the column adding script.

    The below query is not working fine.Else block is firing,even though the column not exists.

    If I run this query without else condition it is working fine.

    May I know why It is not running if we have else statement.

    IF NOT  EXISTS(SELECT * FROM sys.columns WHERE Name = N'salary' and Object_ID = Object_ID(N'tablenameEmployee'))
          BEGIN
            ALTER TABLE tablenameEmployee ADD salary VARCHAR(1) NOT NULL DEFAULT'd'
          END

    ELSE
          BEGIN
            UPDATE tablenameEmployee SET salary ='D' where isnull(salary,'')=''
          END

    Thanks

    Chandu


    chandu


    • Edited by chandu203 Friday, June 1, 2012 6:57 AM
    Friday, June 1, 2012 5:50 AM

Answers

  • The issue is not that the ELSE block is not executing but that the UPDATE statement will cause a compile error if the column does not exist when the entire batch is compiled.  Conditional DDL is problematic in this regard.  You can use dynamic SQL to workaround the problem:

    IF NOT  EXISTS(SELECT * FROM sys.columns WHERE Name = N'salary' and Object_ID = Object_ID(N'dbo.tablenameEmployee'))
           BEGIN 
            ALTER TABLE dbo.tablenameEmployee ADD salary VARCHAR(1) NOT NULL DEFAULT'd' 
          END 
    
    ELSE 
          BEGIN 
            EXEC(N'UPDATE dbo.tablenameEmployee SET salary =''D'' where isnull(salary,'''')=''''');
          END 
    


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Aalamjeet Rangi Monday, June 4, 2012 7:37 AM
    • Marked as answer by Maggie Luo Monday, June 18, 2012 2:05 PM
    Friday, June 1, 2012 12:11 PM
    Answerer
  • The problem is Initially the column doesn't exists then the IF part will execute and skip the ELSE part. So you may add a block like below

    As I mentioned in my earlier reply, the problem is because the referenced column is missing when the batch is compiled.  No code in the batch is executed due to the compilation error so conditional logic will not avoid the issue.  The only way to add and reference the new column in the same batch is with dynamic SQL.

    If you run your script against the table without the column, you will see the compilation error:

    USE tempdb;
    GO
    CREATE TABLE dbo.tablenameEmployee (
    	ExistingColumn int	
    	);
    GO
    
    IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'salary' and object_id = OBJECT_ID(N'dbo.tablenameEmployee')) 
    BEGIN 
    	ALTER TABLE dbo.tablenameEmployee 
    		ADD salary VARCHAR(1) NOT NULL DEFAULT'd' ;  
    	IF EXISTS(SELECT TOP 1 * FROM dbo.tablenameEmployee where isnull(salary,'')='')   
    	BEGIN    
    		UPDATE dbo.tablenameEmployee SET salary ='D' where isnull(salary,'')='' ;  
    	END
    END
    ELSE
    BEGIN    
    	UPDATE dbo.tablenameEmployee 
    	SET salary ='D' 
    	where isnull(salary,'')='' ;
    END

    The result is:

    Msg 207, Level 16, State 1, Line 6
    Invalid column name 'salary'.
    Msg 207, Level 16, State 1, Line 8
    Invalid column name 'salary'.
    


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by Maggie Luo Monday, June 18, 2012 2:05 PM
    Monday, June 4, 2012 12:07 PM
    Answerer

All replies

  • Hi Chandu,

    When you run your script without new column (salary) during compilation SQL identifies no salary column for your table in Update statement.  So, you cannot have Update query in condition where you create new column.

    You might need to have this update query separately to get this working fine.


    With Thanks and Regards
    Sambath Raj.C
    click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you

    Friday, June 1, 2012 9:19 AM
  • You can try this:

    IF NOT  EXISTS(SELECT * FROM sys.columns WHERE Name = N'salary' and Object_ID = Object_ID(N'tablenameEmployee')) 
    BEGIN 
    	ALTER TABLE tablenameEmployee ADD salary VARCHAR(1) NOT NULL DEFAULT'd' 
    END 
    UPDATE tablenameEmployee SET salary ='D' where isnull(salary,'')='' 

    If Column is not there add the new column.  Anyways you will update if column exists when it is empty.  Just remove the ELSE block

    With Thanks and Regards
    Sambath Raj.C
    click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you

    Friday, June 1, 2012 9:23 AM
  • Hi,

    Do as follows

    IF NOT  EXISTS(SELECT * FROM sys.columns WHERE Name = N'salary' and Object_ID = Object_ID(N'tablenameEmployee'))
          BEGIN
            ALTER TABLE tablenameEmployee ADD salary VARCHAR(1) NOT NULL DEFAULT'd'
          END

    ELSE
          BEGIN
            UPDATEtablenameEmployee SET salary ='D' where salary is null 
          END


    PS.Shakeer Hussain


    Friday, June 1, 2012 9:54 AM
  • The issue is not that the ELSE block is not executing but that the UPDATE statement will cause a compile error if the column does not exist when the entire batch is compiled.  Conditional DDL is problematic in this regard.  You can use dynamic SQL to workaround the problem:

    IF NOT  EXISTS(SELECT * FROM sys.columns WHERE Name = N'salary' and Object_ID = Object_ID(N'dbo.tablenameEmployee'))
           BEGIN 
            ALTER TABLE dbo.tablenameEmployee ADD salary VARCHAR(1) NOT NULL DEFAULT'd' 
          END 
    
    ELSE 
          BEGIN 
            EXEC(N'UPDATE dbo.tablenameEmployee SET salary =''D'' where isnull(salary,'''')=''''');
          END 
    


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Aalamjeet Rangi Monday, June 4, 2012 7:37 AM
    • Marked as answer by Maggie Luo Monday, June 18, 2012 2:05 PM
    Friday, June 1, 2012 12:11 PM
    Answerer
  • Hi,

    The problem is Initially the column doesn't exists then the IF part will execute and skip the ELSE part. So you may add a block like below

    IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'salary' and Object_ID = Object_ID(N'tablenameEmployee')) BEGIN ALTER TABLE tablenameEmployee ADD salary VARCHAR(1) NOT NULL DEFAULT'd' ;
    IF EXISTS(SELECT TOP 1 FROM tablenameEmployee where isnull(salary,'')='')
    BEGIN
    UPDATE tablenameEmployee SET salary ='D' where isnull(salary,'')='' ;
    END
    END
    ELSE

    BEGIN
    UPDATE tablenameEmployee SET salary ='D' where isnull(salary,'')='' ;
    END


    Regards
    Satheesh

    Monday, June 4, 2012 7:17 AM
    Answerer
  • The problem is Initially the column doesn't exists then the IF part will execute and skip the ELSE part. So you may add a block like below

    As I mentioned in my earlier reply, the problem is because the referenced column is missing when the batch is compiled.  No code in the batch is executed due to the compilation error so conditional logic will not avoid the issue.  The only way to add and reference the new column in the same batch is with dynamic SQL.

    If you run your script against the table without the column, you will see the compilation error:

    USE tempdb;
    GO
    CREATE TABLE dbo.tablenameEmployee (
    	ExistingColumn int	
    	);
    GO
    
    IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'salary' and object_id = OBJECT_ID(N'dbo.tablenameEmployee')) 
    BEGIN 
    	ALTER TABLE dbo.tablenameEmployee 
    		ADD salary VARCHAR(1) NOT NULL DEFAULT'd' ;  
    	IF EXISTS(SELECT TOP 1 * FROM dbo.tablenameEmployee where isnull(salary,'')='')   
    	BEGIN    
    		UPDATE dbo.tablenameEmployee SET salary ='D' where isnull(salary,'')='' ;  
    	END
    END
    ELSE
    BEGIN    
    	UPDATE dbo.tablenameEmployee 
    	SET salary ='D' 
    	where isnull(salary,'')='' ;
    END

    The result is:

    Msg 207, Level 16, State 1, Line 6
    Invalid column name 'salary'.
    Msg 207, Level 16, State 1, Line 8
    Invalid column name 'salary'.
    


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by Maggie Luo Monday, June 18, 2012 2:05 PM
    Monday, June 4, 2012 12:07 PM
    Answerer
  • Hi,

    I think i overlooked that. Thanks for  clarifying that

    Regards
    Satheesh

    Monday, June 4, 2012 2:16 PM
    Answerer