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]

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
www.teched.hr
"tperovic" <tp******@compumation.com> wrote in message news:vTjpb.91367$e01.318593@attbi_s02...

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

[Simon Hayes]

"tperovic" <tp******@compumation.com> wrote in message news:<vTjpb.91367$e01.318593@attbi_s02>...

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

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]

I looked in BOL but couldn't find it.

Hvala vam za informaciju.

"Dean Savovic" <aj****************@dati.hmhmh.com> wrote in message
news:bo**********@brown.net4u.hr...

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
www.teched.hr
"tperovic" <tp******@compumation.com> wrote in message

news:vTjpb.91367$e01.318593@attbi_s02...

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]

Nema na cemu.

--
Dean Savovic
www.teched.hr
"tperovic" <tp******@compumation.com> wrote in message news:8EXpb.108069$Fm2.92888@attbi_s04...

I looked in BOL but couldn't find it.

Hvala vam za informaciju.

"Dean Savovic" <aj****************@dati.hmhmh.com> wrote in message
news:bo**********@brown.net4u.hr...

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
www.teched.hr
"tperovic" <tp******@compumation.com> wrote in message

news:vTjpb.91367$e01.318593@attbi_s02...

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