CODEWITHSUNDEEP
sqlsql-server-2012
James
James

Reputation: 1945

Not able to alter column after renaming it

I am first checking if column exist or not. If not then i rename existing column and then try to change its datatype. But when i run script i get error Invalid column name Age

if Not exists(select * from sys.columns 
        where Name = 'Age' and Object_ID = Object_ID('TestTable'))
begin

EXEC sp_RENAME 'TestTable.Name' , 'Age', 'COLUMN'

ALTER TABLE TestTable ALTER COLUMN Age int

Update TestTable set Age = 0

ALTER TABLE TestTable ALTER COLUMN Age int Not Null

end

What am i doing wrong here?

Upvotes: 0

Views: 86

Answers (1)

SelvaS
SelvaS

Reputation: 2125

Try this. By splitting your If condition with Not Exists and exists.

if Not exists(select * from sys.columns 
        where Name = 'Age' and Object_ID = Object_ID('TestTable'))
BEGIN

EXEC sp_RENAME 'TestTable.Name' , 'Age', 'COLUMN'
END
GO

if exists(select * from sys.columns 
        where Name = 'Age' and Object_ID = Object_ID('TestTable'))
begin
ALTER TABLE TestTable ALTER COLUMN Age int

Update TestTable set Age = 0

ALTER TABLE TestTable ALTER COLUMN Age int Not Null
END
GO

Edit:

About Batch in SQL from here

A table cannot be changed and then the new columns referenced in the same batch.

So, renaming and altering in the same batch not worked.

See here

When an error occurs while executing a batch of SQL statements,

  • No statements in the batch are executed.

Upvotes: 1

Related Questions