Temporary tables

[tperovic]

If a stored procedure invokes another stored procedure that creates a
temporary table why can't the calling procedure see the temporary table?


CREATE PROCEDURE dbo.GetTemp
AS
CREATE TABLE #Test
(
[id] int not null identity,
[name] as char(4)
)
INSERT INTO #Test ([name]) VALUES ('Test')


CREATE PROCEDURE dbo.Test
AS
EXEC dbo.GetTemp
SELECT * FROM #Test -- Invalid object name '#Test'.

Thanks,
TP

[Dean Savovic]

Re: Temporary tables

SQL 2000&7.0 BOL:
"A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can
be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be
referenced by the process which called the stored procedure that created the table."

Try creating temp table in dbo.Test stored procedure. That way you'll be able to insert records into it
in dbo.GetTemp stored procedure and fetch those records in dbo.Test.

--
Dean Savovic

https://www.teched.hr

"tperovic" <tperovic@compu mation.com> wrote in message news:vTjpb.9136 7$e01.318593@at tbi_s02...[color=blue]
> If a stored procedure invokes another stored procedure that creates a
> temporary table why can't the calling procedure see the temporary table?
>
>
> CREATE PROCEDURE dbo.GetTemp
> AS
> CREATE TABLE #Test
> (
> [id] int not null identity,
> [name] as char(4)
> )
> INSERT INTO #Test ([name]) VALUES ('Test')
>
>
> CREATE PROCEDURE dbo.Test
> AS
> EXEC dbo.GetTemp
> SELECT * FROM #Test -- Invalid object name '#Test'.
>
> Thanks,
> TP
>
>[/color]

[Simon Hayes]

Re: Temporary tables

"tperovic" <tperovic@compu mation.com> wrote in message news:<vTjpb.913 67$e01.318593@a ttbi_s02>...[color=blue]
> If a stored procedure invokes another stored procedure that creates a
> temporary table why can't the calling procedure see the temporary table?
>
>
> CREATE PROCEDURE dbo.GetTemp
> AS
> CREATE TABLE #Test
> (
> [id] int not null identity,
> [name] as char(4)
> )
> INSERT INTO #Test ([name]) VALUES ('Test')
>
>
> CREATE PROCEDURE dbo.Test
> AS
> EXEC dbo.GetTemp
> SELECT * FROM #Test -- Invalid object name '#Test'.
>
> Thanks,
> TP[/color]

A temp table created in a stored procedure is dropped when the
procedure completes - see CREATE TABLE in Books Online. You can either
create the table in the 'outer' stored procedure, or perhaps some of
the approaches described here may be useful:

http://www.algonet.se/~sommar/share_data.html

Simon

[tperovic]

Re: Temporary tables

I looked in BOL but couldn't find it.

Hvala vam za informaciju.

"Dean Savovic" <ajmisadposalji spam@dati.hmhmh .com> wrote in message
news:bo55uo$sig $1@brown.net4u. hr...[color=blue]
> SQL 2000&7.0 BOL:
> "A local temporary table created in a stored procedure is dropped[/color]
automatically when the stored procedure completes. The table can[color=blue]
> be referenced by any nested stored procedures executed by the stored[/color]
procedure that created the table. The table cannot be[color=blue]
> referenced by the process which called the stored procedure that created[/color]
the table."[color=blue]
>
> Try creating temp table in dbo.Test stored procedure. That way you'll be[/color]
able to insert records into it[color=blue]
> in dbo.GetTemp stored procedure and fetch those records in dbo.Test.
>
> --
> Dean Savovic
> www.teched.hr
>
>
> "tperovic" <tperovic@compu mation.com> wrote in message[/color]
news:vTjpb.9136 7$e01.318593@at tbi_s02...[color=blue][color=green]
> > If a stored procedure invokes another stored procedure that creates a
> > temporary table why can't the calling procedure see the temporary table?
> >
> >
> > CREATE PROCEDURE dbo.GetTemp
> > AS
> > CREATE TABLE #Test
> > (
> > [id] int not null identity,
> > [name] as char(4)
> > )
> > INSERT INTO #Test ([name]) VALUES ('Test')
> >
> >
> > CREATE PROCEDURE dbo.Test
> > AS
> > EXEC dbo.GetTemp
> > SELECT * FROM #Test -- Invalid object name '#Test'.
> >
> > Thanks,
> > TP
> >
> >[/color]
>
>[/color]

[Dean Savovic]

Re: Temporary tables

Nema na cemu.

--
Dean Savovic

https://www.teched.hr

"tperovic" <tperovic@compu mation.com> wrote in message news:8EXpb.1080 69$Fm2.92888@at tbi_s04...[color=blue]
> I looked in BOL but couldn't find it.
>
> Hvala vam za informaciju.
>
> "Dean Savovic" <ajmisadposalji spam@dati.hmhmh .com> wrote in message
> news:bo55uo$sig $1@brown.net4u. hr...[color=green]
> > SQL 2000&7.0 BOL:
> > "A local temporary table created in a stored procedure is dropped[/color]
> automatically when the stored procedure completes. The table can[color=green]
> > be referenced by any nested stored procedures executed by the stored[/color]
> procedure that created the table. The table cannot be[color=green]
> > referenced by the process which called the stored procedure that created[/color]
> the table."[color=green]
> >
> > Try creating temp table in dbo.Test stored procedure. That way you'll be[/color]
> able to insert records into it[color=green]
> > in dbo.GetTemp stored procedure and fetch those records in dbo.Test.
> >
> > --
> > Dean Savovic
> > www.teched.hr
> >
> >
> > "tperovic" <tperovic@compu mation.com> wrote in message[/color]
> news:vTjpb.9136 7$e01.318593@at tbi_s02...[color=green][color=darkred]
> > > If a stored procedure invokes another stored procedure that creates a
> > > temporary table why can't the calling procedure see the temporary table?
> > >
> > >
> > > CREATE PROCEDURE dbo.GetTemp
> > > AS
> > > CREATE TABLE #Test
> > > (
> > > [id] int not null identity,
> > > [name] as char(4)
> > > )
> > > INSERT INTO #Test ([name]) VALUES ('Test')
> > >
> > >
> > > CREATE PROCEDURE dbo.Test
> > > AS
> > > EXEC dbo.GetTemp
> > > SELECT * FROM #Test -- Invalid object name '#Test'.
> > >
> > > Thanks,
> > > TP
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]