Microsoft KB Archive/101666

From BetaArchive Wiki



BUG: Docerr: Nested Stored Procedure and Temporary Objects

Last reviewed: April 28, 1997
Article ID: Q101666

The information in this article applies to:

  • Microsoft SQL Server version 4.2 for OS/2

BUG# 8899 (4.2)

SYMPTOMS

On page 292 of Transact-SQL "User's Guide," it is incorrectly stated that:

   If you execute a procedure that calls another procedure, the called
   procedure can access all objects except temporary tables created by
   the first procedure.

However, the called procedure can access the temporary tables created by the parent procedure. Therefore, the correct statement should be:

   If you execute a procedure that calls another procedure, the called
   procedure can access all objects created by the first procedure.

However, there is one exception to this case. When the called procedure contains a conditional CREATE TABLE statement for the same temporary table name, then the called procedure fails to find the temporary object created by calling procedure.

Example

For example, consider the following where proca is the calling procedure and procb is the called procedure:

   create proc proca as
        create table #t1 (i int)
        execute procb
   go
   create table #t1 (i int)
   go
   create proc procb as
        if not exists (select name from tempdb..sysobjects
           where substring(name,1,3)='#t1')
           create table #t1 (i int)
   select * from #t1
   go
   drop table #t1
   go
   execute proca
   go

In this case, the execution of procedure proca fails indicating that the object #t1 cannot be found. However, if there is no CREATE statement in the IF EXIST clause, then the above script executes without any errors.



Additional query words:

Keywords : kbbug4.20 kbother SSrvDoc_Err SSrvServer
Version : 4.2
Platform : OS/2
Issue type : kbdocerr


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.