aaron.reese (9/14/2012)
do you mean global ##temptablesThese persist between stored procedures until the session is ended or the connection is dropped.
you can fill a ##temptable in one procedure and access the data in another, but it is generally not good practice.
That is not exactly correct...
First of all, it can be done even with local #temptable. If you create this table in one proc you can use it in a subsequent "chained" proc. Try this:
create proc p1
as
begin
create table #tP (a int)
exec p2
select * from #tP
end
go
create proc p2
as
begin
insert #tP select 11111
end
go
exec p1
go
The second thing about global ##temptables is they are not only accessible by the "chained" proc executed within the one which creates a global temp table, but also it can be accessed by any query in other connection until the connection, which used to create this global temptable is closed or any other process dropped it.