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