scope of temp table 
Author Message
 scope of temp table

Hi there,

My question is about the scope of the temp table.  "Inside SQL server"
states "It exists for the life of that connection ". Following is small test
that show a temp table created in stored procedure only exists within the
stored proc. not connection.

---------------------------------------- test
code--------------------------------------

use pubs

go

Create proc sp_temptest

as

 select * into #tempauthors from authors

go

sp_temptest

select * from #tempauthors

-------------------------------------------result
as ---------------------------------------------------

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#tempauthors'.

----------------------------------------------------------------------------
-------------

Is there a way to make the temp table created from SP be visible outside the
SP?  global temp table is not option due to other connections can see it.

Thanks

Jianxin



Wed, 24 Apr 2002 03:00:00 GMT
 scope of temp table

.... unless the temp table is created in a stored procedure, in which case
the temp table is dropped when the procedure ends.

This should be well documented in Books Online, and I'll put it in the error
log for Inside...

Thanks!
--
------------------------------------
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com

Feed Someone for Free Today:
     www.TheHungerSite.com


Quote:
> Hi there,

> My question is about the scope of the temp table.  "Inside SQL server"
> states "It exists for the life of that connection ". Following is small
test
> that show a temp table created in stored procedure only exists within the
> stored proc. not connection.

> ---------------------------------------- test
> code--------------------------------------

> use pubs

> go

> Create proc sp_temptest

> as

>  select * into #tempauthors from authors

> go

> sp_temptest

> select * from #tempauthors

> -------------------------------------------result
> as ---------------------------------------------------

> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name '#tempauthors'.

> --------------------------------------------------------------------------
--
> -------------

> Is there a way to make the temp table created from SP be visible outside
the
> SP?  global temp table is not option due to other connections can see it.

> Thanks

> Jianxin



Wed, 24 Apr 2002 03:00:00 GMT
 scope of temp table
To be more specific,

In SPS:
1)    If you create a temporary table #sometable, it is local to the SP &
gets dropped after the SP completes.

        create proc #s as create table #t( i int)
        go
        exec #s
        select * from #t

2)    If you create a ##sometable, it is global to SQL Server & to the
connection. It gets dropped automatically when the connection is
disconnected. Try the SELECT * FROM ##t2 from another connection also.

        alter proc #s as create table ##t( i int)
        go
        exec #s
        select * from ##t

3)    If you create a #sometable in a dynamic SQL statement in a SP, it is
only visible inside the dynamic SQL batch.

        alter proc #s as exec('create table #t( i int)') select * from #t
        go
        exec #s

4)    If you create a ##sometable in a dynamic SQL statement in a SP, it is
global to SQL Server & to the connection. Try the SELECT * FROM ##t2 from
another connection also.

        alter proc #s as exec('create table ##t( i int)') select * from ##t
        go
        exec #s
        select * from ##t

Otherwise,

5)  If you create a #sometable in a batch, it is local to the connection. It
gets dropped automatically when the connection is disconnected.

        create table #t( i int)
        go
        alter proc #s as insert #t values(1)
        go
        exec #s
        select * from #t

6)  If you create a #sometable in a dynamic SQL batch, it is visible only
inside the batch.
        exec('create table #t1( i int)')
        go
        alter proc #s as insert #t1 values(1)
        go
        exec #s
        select * from #t1

7)  If you create a ##sometable in a dynamic SQL statement or in a batch, it
is global to SQL Server & the connection. It gets dropped automatically when
the connection is disconnected. Try the SELECT * FROM ##t2 from another
connection also.

        exec('create table ##t2( i int)')
        go
        alter proc #s as insert ##t2 values(1)
        go
        exec #s
        select * from ##t2

--
Umachandar Jayachandran
MCDBA, MCSE + Internet, MCP + Internet, MCSE



Thu, 25 Apr 2002 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. scope of temp tables created dinamicaly

2. temp table scope

3. Temp table scope within stored procedures`

4. Temp tables scope

5. Temp table scope within stored procedures

6. #Temp tables scope....Need practical suggestions, not bookish

7. Temp table scope within stored procedures

8. Global Temp Table Scope Issue

9. Temp table vs Global Temp table

10. returning temp result of temp table to ADO/ASP

11. Difference between create table #temp and ##temp ?

12. temp table problem with global temp option


 
Powered by phpBB® Forum Software