BUG: WITH RECOMPILE Not Working for Temporary Tables

• Microsoft SQL Server version 4.2 for OS/2
• Microsoft SQL Server, versions 4.21 and 4.21a

       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

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

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

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

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

CAUSE

SQL Server incorrectly recompiles the stored procedures created with the recompile option if the referenced objects are temporary tables.

WORKAROUND

Use permanent tables in place of temporary tables, or drop and recreate the stored procedure if the temporary tables are dropped and recreated.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2 and Microsoft SQL Server versions 4.21 and 4.21a . We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.