Microsoft KB Archive/87835

= BUG: WITH RECOMPILE Not Working for Temporary Tables =

Article ID: 87835

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q87835



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   --- inside insert

And a simple SELECT on table #t returns:

  i1                  i2   -          --- 1                 100

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 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: kbbug kbprogramming KB87835

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.