BUG: WITH RECOMPILE Not Working for Temporary Tables (87835)

BUG# NT:  876 (4.21)
		

SYMPTOMS

The WITH RECOMPILE clause for stored procedures does not function properly if the objects referenced by the stored procedure are temporary tables created outside the stored procedure but in the same session.

When you execute a stored procedure created with the RECOMPILE option, the procedure uses the original table structure created before the stored procedure is created, even if the temporary table has been structurally changed since the first execution of the stored procedure.

For instance, the following query

   create table #t (c char(20))
   insert #t values ('outside insert')
   go
   create proc p1 WITH RECOMPILE as
   insert #t values ('inside insert')
   select *  from #t
   go
   exec p1
   go
				

returns these correct results before the table is changed:

   c
   ----------
   outside insert
   inside insert
				

However, if the table is changed as follows:

   drop table #t
   go
   create table #t(i1 int, i2 int)
   insert #t values(1,100)
   go
				

then the stored procedure p1 returns:

   c
   -----------
   <some garbage>
   inside insert
				

And a simple SELECT on table #t returns:

   i1                  i2
   ---------          -----------
   1                  100
   <some big number>  <some big number>
				

If you also use the WITH RECOMPILE clause when executing the stored procedure, the problem still exists.