I have a a number of sp's that create a temporary table
CREATE PROCEDURE [dbo].[tempdata_proc]
@NeedAvg tinyint = 0
SET NOCOUNT ON;
if @NeedAvg = 1
Update #TempData set AvgValue = 1
Update #TempData set Value = -1;
Create table #TempData
, Value float
--, AvgValue float
Create clustered index IXTemp on #TempData(tele_time);
insert into #TempData(tele_time, Value ) values( GETDATE(), 50 ); --sample data
@ID = 1000,
@UpdAvg = 1
Exec dbo.tempdata_proc @ID, @UpdAvg ;
select * from #TempData;
drop table #TempData
The way that stored procedures are processed is split into two parts - one part, checking for syntactical correctness, is performed at the time that the stored procedure is created or altered. The remaining part of compilation is deferred until the point in time at which the store procedure is executed. This is referred to as Deferred Name Resolution and allows a stored procedure to include references to tables (not just limited to temp tables) that do not exist at the point in time that the procedure is created.
Unfortunately, when it comes to the point in time that the procedure is executed, it needs to be able to compile all of the individual statements, and it's at this time that it will discover that the table exists but that the column doesn't - and so at this time, it will generate an error and refuse to run the procedure.
The T-SQL language is unfortunately a very simplistic compiler, and doesn't take runtime control flow into account when attempting to perform the compilation. It doesn't analyse the control flow or attempt to defer the compilation in conditional paths - it just fails the compilation because the column doesn't (at this time) exist.
Unfortunately, there aren't any mechanisms built in to SQL Server to control this behaviour - this is the behaviour you get, and anything that addresses it is going to be perceived as a workaround - as evidenced already by the (valid) suggestions in the comments - the two main ways to deal with it are to use dynamic SQL or to ensure that the temp table always contains all columns required.
One way to workaround your concerns about maintenance if you go down the "all uses of the temp table should have all columns" is to move the column definitions into a separate stored procedure, that can then augment the temporary table with all of the required columns - something like:
create procedure S_TT_Init as alter table #TT add Column1 int not null alter table #TT add Column2 varchar(9) null go create procedure S_TT_Consumer as insert into #TT(Column1,Column2) values (9,'abc') go create procedure S_TT_User as create table #TT (tmp int null) exec S_TT_Init insert into #TT(Column1) values (8) exec S_TT_Consumer select Column1 from #TT go exec S_TT_User
Which produces the output
9. You'd put your temp table definition in
S_TT_Consumer is the inner query that multiple stored procedures call, and
S_TT_User is an example of one such stored procedure.