Insidious Bug?

This looks like a bug to me, can someone verify that this is faulty behavior or tell me why it is proper?

Two stored procedures are defined, each creates a temporary table of the same name, but with different definitions. Both procedures compile successfully. If procedure Two is executed by itself, errors are produced. Once procedure One has been run, then both procedures succeed.

It seems to be an issue of improperly resolving the definition of the temporary table.

Thanks

drop procedure one

go

create procedure one

as

begin

create table #salt(

a int not null,

b int not null,

c varchar(100)

)

insert #salt values( 1, 1, 'Fred' )

insert #salt values( 1, 2, 'Barney' )

insert #salt values( 1, 3, 'Wilma' )

insert #salt values( 2, 1, 'Betty' )

insert #salt values( 2, 2, 'Pebbles' )

insert #salt values( 2, 3, 'BamBam' )

insert #salt values( 3, 1, 'Dino' )

Selecta,

b

From#salt

end

go

drop procedure two

go

create procedure two

as

begin

create table #salt(

a int not null,

b int not null

)

insert #salt

Execone

Select*

From#salt

end

go

--Exec one

go

Exec two

go

This is by design and has to do with how we compile modules/batches. When you run SP two for the first time without running one before, we will try to compile the definition of SP two. And when we hit the call to SP one we will try to compile it. At this point the temporary table #salt exists and the compilation will use that as definition. This needs to be done to allow scenarios where you can create a temporary table in one SP and reference it any SPs that you call without creating it. The error message happens now because the table definition is different and/or you already have the object created. If you run SP one first then there is no temporary table at the time of compilation and the definition inside the SP will be used. This plan is cached and it will be reused later when you call SP two so it will work fine. Part of this has to do with deferred name resolution and compilation which will kick-in here because of the use of temporary table.

The moral is that you should avoid creating temporary tables with same name and different definitions in multiple SPs that will call one another. The results can often be unpredictable if you don't know the exact call order. And if you have statements or constructs that will be deferred to run-time for resolution/compilation it becomes even more problematic.

|||

Thanks for the information. It certainly explains the problem.

However, it really seems like a bad design or at least a lazy one. There is a different meaning to two procedures creating temporary tables and two procedures sharing temp tables. Furthermore, the meaning of each case is clear to the compiler as one contains a new Create Table at the inner level. And this design is inconsistent with the rest of the implementation. Once the second Create Table is executed there are two temp tables. In other words, the compiler is forcing the table to share definitions even though they do not share the same instances.

As a practical matter, this behavior is very difficult to deal with. The very fact that inconsistent behavior is produced based on the order of execution points that out. The error message received can vary wildly based on the actual statements involved. I encountered this by calling a procedure written by someone sharing my office and it was more a wild stab that I got it fixed. Imagine calling code delivered with a purchased package. It does not seem reasonable that a developer should have to be aware of the implementation of every stored procedure they might call.

Thanks again for the explanation.