Q:

Session-global temporary tables in SQL Server

In SQL Server, temporary tables with a name like #temp has a local scope. If you create them in your session, everything in your session can see them, but not outside your session. If you create such a table within a stored procedure, the scope is local to that procedure. So when the proc exits, the table vanishes.
The only alternative I am aware of, is to use tables with a name like ##temp. These are temporary, but are visible server-wide. So if I create the table in my session, Bob in the office next door will also see them.
What I am looking for is somewhere in the middle, so I can create the table within a stored procedure and have that table be available to my session even after the stored proc exits. The nearest I have been able to find, is to create the table with only one field on it and then alter it within the stored proc. That seems like a bit of a kludge, though.

A:

Another kludge that may work for you - it depends on how many temp tables are involved here.
Create your temp tables as real tables, with an extra column called SPID, defaulting to @@SPID.
Then create a view that accesses these tables, but filters based on the @@SPID value. All operations that take place through this view should look as if they're isolated on a per-session basis. E.g.:
create table temp_Boris (
    SPID int default @@SPID,
    ColA int,
    ColB varchar(10)
)
go
create view vBoris
as
    select ColA,ColB from temp_Boris where SPID = @@SPID
go

Then, on one connection, run the following:
insert into vBoris(ColA,ColB)
select 10,'abc' union all
select 20,'def'
go
select * from vBoris

And on another connection, run the following:
insert into vBoris(ColA,ColB)
select 10,'abc' union all
select 20,'def'
go
select * from vBoris
select * from temp_Boris
go
delete from vBoris
go
select * from vBoris
select * from temp_Boris

And you'll see that each connection is able to treat "vBoris" somewhat like a temp table - of course, you might need to add additional routines around this (and possibly more columns) to clear the table of old/stale results.
Okay, I'll admit, it feels ugly too.

A:

Could you not create the table when you start the session, then execute the stored proc, then do whatever else you want to do to the table after the stored proc executes?

