469,088 Members | 1,273 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,088 developers. It's quick & easy.

Temporary tables

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
Jul 20 '05 #1
4 14235
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

Jul 20 '05 #2
"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
Jul 20 '05 #3
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


Jul 20 '05 #4
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



Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Zlatko Matić | last post: by
1 post views Thread by Stefan van Roosmalen | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.