Adding and updating columns a posteriori in temp tables in SQL Server

Question

I'm trying to alter an existing temp table that was created and populated in the stored procedure calling this one where I'm doing these changes. I can't change the calling stored procedure and I need to add columns to the temp table, so I tried this:

ALTER TABLE #MyTemp 
ADD Column1 VARCHAR(100);           

UPDATE x
SET Column1 = a.SomeColumn
FROM #MyTemp x
INNER JOIN dbo.AnotherTable a (NOLOCK) ON a.ColumnName = x.ColumnName 
WHERE somecondition;

It compiles, but when I run it I get:

Msg 207, Level 16, State 1, Procedure ProcName, Line #
Invalid column name 'Column1'

Looks like this code is not even being executed.

Could someone please tell me if this is possible and how?

Thanks.

Answer 1

You can apparently do this, but you can't insert into the altered temp table in the same scope you alter it. Don't ask me why.

You can use dynamic sql in the the second proc to get to a lower nesting level, or call another proc. Like this:

use tempdb
go

create or alter proc a
as
begin
  create table #t(id int)
  exec b
  select * from #t
end

go

create or alter proc b
as
begin
   alter table #t add a int

   exec c

end

go

create or alter proc c
as
begin
   insert into #t(id,a) values (1,1)

end

go

Answer 2

As David Browne mentioned on his answer above, this can also be achieved by using dynamic sql, but there is a catch, and here is how:

DECLARE @ColName1 NVARCHAR(100)
DECLARE @DynamicSQL NVARCHAR(500)

SET @ColName1='Column1'
SET @DynamicSQL = 'ALTER TABLE #MyTemp ADD ['+ CAST(@ColName1 AS NVARCHAR(100)) +'] 
NVARCHAR(100) NULL; '

EXEC(@DynamicSQL)

SET @DynamicSQL = 'UPDATE x SET '+ CAST(@ColName1 AS NVARCHAR(100)) + = a.SomeColumn
FROM #MyTemp x INNER JOIN dbo.AnotherTable a (NOLOCK) ON a.ColumnName = x.ColumnName 
WHERE somecondition;

EXEC(@DynamicSQL)

If you place the Alter and the Update in the same execution it won't work, they can't be executed at the same time, otherwise is the same problem as the static code all over again. This way is like a simulation of the extra call to another stored proc but without having an extra proc.