473,372 Members | 1,608 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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 14380
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Soefara | last post by:
Dear Sirs, I have been developing an application on Windows with MySQL 3.23, making use of temporary tables. Now when I try to port the application to a Unix box running also MySQL 3.23, I...
2
by: Ryan | last post by:
Just a quicky about temporarary tables. If using QA, when you create a temporary table, it gets dropped if you close the query. Otherwise you need to state 'DROP TABLE myTable' so that you can...
11
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the...
1
by: Sampath Reddy | last post by:
Hi Everybody, We are using UDB v8.1 I will explain about my Stored procedures which we are executing in UDB AIX box. We have 3 millions(apporox) of data in 22 tables. By applying the business...
2
by: Keith Watson | last post by:
Hi, we are currently implementing an application running on DB2 V7 on Z/OS using largely COBOL stored procedures, managed using WLM. Some of these stored procedures declared global temporary...
2
by: Chuck Crews | last post by:
I am interested in declaring a global temporary table within an application. The application processes 1 set of 600 or less rows each iteration. Multiple programs can and do call this one...
3
by: Mike Ridley | last post by:
I have 2 databases called (for example) "progs.mdb" and "files.mdb". Both these databases reside on computer "myserver". The progs database has links to the tables in the files database....
0
by: Zlatko Matić | last post by:
I tried to work with postgres temporary tables from MS Access, but unsuccessfully... I was able to create temporary table by pass-through query, also I succeeded in creating linked table through...
1
by: Stefan van Roosmalen | last post by:
Hi there, Is there a way to list the TEMPORATY tables? I have tried SHOW TABLES, but this command only list the regular tables. Thank you very much for your answer. Regards, Stefan.
5
by: Rahul B | last post by:
Hi, I have very little knowledge about creating Procedures/functions in DB2. When i tried to create the test function like CREATE FUNCTION GET_TEST (P_TEST_ID INTEGER, P_SEL_OR_SORT...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.