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

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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/

Answer 5

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

Answer 6

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/

Answer 7

Hi,

I think i overlooked that. Thanks for  clarifying that

Regards
Satheesh