Adding a column to a table then updating the table

Question

I have a code that looks something like this

declare @query = '
   Alter Table Temp
   Add NewColumn int

   update Temp
   set NewColumn = 100'

exec (@query)

It will give error NewColumn does not exist. But if I run this normally without using @query = '' it runs fine. Also, what is it called when you run the code like this with the @query = ' text ' then exec?

Answer 1

You are executing them as one batch in dynamic SQL. So the compiler will not allow it, because at compilation time the column does not exist yet.

It is not true that it would work when executed together. you are executing them line-by-line in separate batches.

• This works: db<>fiddle

   Alter Table Temp
   Add NewColumn int

   update Temp
   set NewColumn = 100

• This does not: db<>fiddle

   Alter Table Temp
   Add NewColumn int

   update Temp
   set NewColumn = 100

So you need to do this

declare @query nvarchar(max) = '
   Alter Table Temp
   Add NewColumn int
';

exec sp_executesql @query;

set @query = '
   update Temp
   set NewColumn = 100
';

exec sp_executesql @query;

db<>fiddle

The ALTER could also go outside the dynamic SQL as well.