I wrote a script to add a column in a table and on successful execution update its values. I know it’s just a simple script but what is wrong with the ‘IF NOT EXISTS’ statement why script between BEGIN and END is not executing and return error . I verified there is no Database level DDL trigger to stop adding column in database.
Msg 207, Level 16, State 1, Line 24
Invalid column name ColumnName.
DECLARE @CloumnAdded bit SET @CloumnAdded = 0 IF NOT EXISTS ( SELECT 1 FROM Sys.Columns WHERE [Object_ID] = Object_ID('DBName.dbo.TableName') AND Name = 'ColumnName' ) BEGIN ALTER TABLE TableName ADD ColumnName varchar(120) SET @CloumnAdded = 1 select @CloumnAdded, 'step 1 ' END IF ( @CloumnAdded = 1 ) BEGIN select @CloumnAdded,'step 2' UPDATE TableName SET ColumnName = '2669' select @CloumnAdded,'step 3' END GO