Problem - to create a temp table in one SP and access it in another

Temp tables are dropped when batch in which they are created goes out of scope.
i.e. the end of the stored procedure or d-sql in which they are created.

Therefore a temp table can be created in one SP and another SP called to access it.
(In v6.5 this needed an unexecuted create table statement in the called sp).
The data is then available in the calling SP.

If the temp table is not created in the outer SP then problems arise.


1) Demonstration of temp table being out of scope
2) Use of temp table within the creation d-sql scope
3) Create temp table with dummy column to expand scope
4) Safer method of above


1) Demonstration of temp table being out of scope 

When this runs it fails as #TestTbl is only available in spTestCreateTbl.
In fact it is only available within the exec statement.

create procedure spTest
as
exec spTestCreateTbl
select 'I am about to access the temp table'
insert #TestTbl (i,j) select 1,1
insert #TestTbl (i,j) select 2,2
select * from #TestTbl
go

create procedure spTestCreateTbl
as
declare @cmd varchar(1000)
set @cmd = 'create table #TestTbl (i int, j int)'
exec(@cmd)
go 

exec spTest
go

drop procedure spTest
go
drop procedure spTestCreateTbl
go


2) Use of temp table within the creation d-sql scope

This calls the SP to access the temp table within the batch that creates it

create procedure spTest2
as
exec spTestCreateTbl2 'spTestDoTbl2'
go

create procedure spTestCreateTbl2
@sp varchar(128)
as
declare @cmd varchar(1000)
set @cmd = 'create table #TestTbl (i int, j int) exec ' + @sp
exec(@cmd)
go
create procedure spTestDoTbl2
as
insert #TestTbl (i,j) select 1,1
insert #TestTbl (i,j) select 2,2
select * from #TestTbl
go

exec spTest2

drop procedure spTest2
go
drop procedure spTestCreateTbl2
go
drop procedure spTestDoTbl2
go

3) Create temp table with dummy column to expand scope
This creates the temp table with a dummy column in the outer SP
The structure is then changed by d-sql in a called SP 
The temp table will still be available in the outer SP - with the changed structute in my version of sql.

create procedure spTest2
as
create table #TestTbl (dummycol int null)
exec spTestCreateTbl2
insert #TestTbl (i,j) select 1,1
insert #TestTbl (i,j) select 2,2
select i,j from #TestTbl
go

create procedure spTestCreateTbl2
as
declare @cmd varchar(1000)
set @cmd = 'alter table #TestTbl add i int, j int'
exec(@cmd)
go

exec spTest2

drop procedure spTest2
go
drop procedure spTestCreateTbl2
go

4) Safer method of above
This is similar to the above except that the temp table access is not made in the outer SP.
This SP is compiled on every run after the changed structure is established and so should be safe.

create procedure spTest2
as
create table #TestTbl (dummycol int null)
exec spTestCreateTbl2
exec spTestDoTbl2
go

create procedure spTestCreateTbl2
as
declare @cmd varchar(1000)
set @cmd = 'alter table #TestTbl add i int, j int'
exec(@cmd)
go
create procedure spTestDoTbl2
as
insert #TestTbl (i,j) select 1,1
insert #TestTbl (i,j) select 2,2
select i,j from #TestTbl
go

exec spTest2

drop procedure spTest2
go
drop procedure spTestCreateTbl2
go
drop procedure spTestDoTbl2
go