Error creating a computed column in SQL Server-CodePudding

I have this question regarding computed columns. This is the first time that I work with them, and I do not understand why I'm getting an error.

Let's assume I have a table already created:

create table testComputed
(
    a int null,
    b int null
)

Now, I want to update it with another couple of columns including a computed one:

alter table testcomputed add c int null;
alter table testcomputed add d int null;
alter table testcomputed add e as c   d;

However, I'm getting this error message:

Msg 207, Level 16, State 1, Line 10
Invalid column name 'c'

Msg 207, Level 16, State 1, Line 10
Invalid column name 'd'

What is missing in the code, why the computed column is not created?

CodePudding user response：

As I mention in the comment, the problem here is that the parsing/compilation is failing. Prior to the batch actually running it is parsed, and only some objects/properties benefit from the parser knowing that the object was created previous in the batch, and thus not failing; columns are not one of these. As such when the batch is parsed, the columns c and d do not exist at that point, and so the the parser generates an error.

The simple solution would be to separate the latter statement into a separate batch. In an application like SSMS that would just mean adding a batch separator (traditionally GO):

ALTER TABLE dbo.testcomputed ADD c int NULL;
ALTER TABLE dbo.testcomputed ADD d int NULL;
GO
ALTER TABLE dbo.testcomputed ADD e AS c   d;
GO

If you need to do it in the same batch, you could defer the parsing by using sys.sp_executsql:

ALTER TABLE dbo.testcomputed ADD c int NULL;
ALTER TABLE dbo.testcomputed ADD d int NULL;
EXEC sys.sp_executesql N'ALTER TABLE dbo.testcomputed ADD e AS c   d;';