need temp table technique

need temp table technique

Post by caud.. » Thu, 20 May 1999 04:00:00



I need a dozen small wrapper stored procedures to be able to call one
big stored procedure that creates a temp table.  The hard part is that
the wrapper stored procedures need to retrieve data from the temp table
after the big procedure has finished.  Normally the temp table would be
gone.  How can I define a temp table in the big stored proc whose scope
remains after the proc is finished but still allows me to keep
concurrent users of the procedures separate?

BTW - SQLServer 6.5
Thanks

--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---

 
 
 

need temp table technique

Post by Trevor Dwye » Thu, 20 May 1999 04:00:00


Create a procedure that creates a table in the tempdb database. This will
remain after the main stored procedure has completed. Although it works I
can not recommend this as good practice. You may want to think about want
you are actually trying to achieve and it may become clear that there is an
easier way.

Example.

CREATE PROCEDURE sp_test
AS
CREATE TABLE tempdb..temp_table (
id IDENTITY (1,1) NOT NULL,
mycolumn VARCHAR(10) NOT NULL
)

INSERT INTO tempdb..temp_table VALUES ('test1')
INSERT INTO tempdb..temp_table VALUES ('test2')
GO

sp_test
GO

SELECT * FROM tempdb..temp_table
GO

Quote:>>id    mycolumn
>>1    test1
>>2    test2

Hope this helps

Best Regards

Trevor Dwyer - SQL Server MVP

--
Trevor Dwyer - SQL Server MVP


> I need a dozen small wrapper stored procedures to be able to call one
> big stored procedure that creates a temp table.  The hard part is that
> the wrapper stored procedures need to retrieve data from the temp table
> after the big procedure has finished.  Normally the temp table would be
> gone.  How can I define a temp table in the big stored proc whose scope
> remains after the proc is finished but still allows me to keep
> concurrent users of the procedures separate?

> BTW - SQLServer 6.5
> Thanks

> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---


 
 
 

need temp table technique

Post by Neil Pik » Thu, 20 May 1999 04:00:00


You can't.  Either the wrapper procs need to create it, or you can create a

Quote:> I need a dozen small wrapper stored procedures to be able to call one
> big stored procedure that creates a temp table.  The hard part is that
> the wrapper stored procedures need to retrieve data from the temp table
> after the big procedure has finished.  Normally the temp table would be
> gone.  How can I define a temp table in the big stored proc whose scope
> remains after the proc is finished but still allows me to keep
> concurrent users of the procedures separate?

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)
 For SQL FAQ entries see
 http://go.compuserve.com/sqlserver (library 1) - latest stuff is always here
 www.ntfaq.com/sql.html
 http://www.swynk.com/faq/sql/sqlserverfaq.asp
 
 
 

need temp table technique

Post by syml.. » Thu, 20 May 1999 04:00:00


In the wrapper create another temp table of the same structure
(allowing for idents etc..), in the main proc SELECT * from #<temptab>
Then use INSERT ... EXEC to populate the 'new' table.

One word of warning,  INSERT EXECS cannot be nested.  In which case do
the same creation but get the main proc to populate the new table in a
dynamic SQL statement.

Dave



> I need a dozen small wrapper stored procedures to be able to call one
> big stored procedure that creates a temp table.  The hard part is that
> the wrapper stored procedures need to retrieve data from the temp
table
> after the big procedure has finished.  Normally the temp table would
be
> gone.  How can I define a temp table in the big stored proc whose
scope
> remains after the proc is finished but still allows me to keep
> concurrent users of the procedures separate?

> BTW - SQLServer 6.5
> Thanks

> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---

--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---
 
 
 

1. Stored Proc Temp table technique

Is there a way to allow a Stored Procedure(a) to execute another SP(b), then
access a temporary table that was created by the called SP(b)?  How can I
make the temp table in SP b retain its data for SP a to read but NOT make it
global.  There will be concurrent users running the process with different
parameters so I can't let their sessions interfere with each other.

BTW - SQLServer Version 6.5.
Thanks

2. Co-existence of SQL Server and Oracle

3. Most efficient technique for temp table data?

4. shape problem

5. Porting SQL Server to IBM OS/2 2.0

6. name/value pairs in a table, need help with reporting techniques

7. Anyone using Ingres ESQLC with Borland C++ on OS/2?

8. Indexing technique needed for large table lookup

9. Temp table vs Global Temp table

10. need help...Using @TABLE variable instead of #temp tables with dynamic SQL (sp_executesql)

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

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