BUG: WITH RECOMPILE Not Working for Temporary Tables

ID: Q87835

The information in this article applies to:

BUG# OS/2: 1379 (4.2)
       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.


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.

Additional query words: stored procedure 


Keywords          : kbprg kbbug4.20 kbbug4.21 kbbug4.21a SSrvServer SSrvWinNT 
Version           : 4.2 | 4.21 4.21a
Platform          : OS/2 WINDOWS 
Issue type        :

Last Reviewed: March 16, 1999