How to access a temporary table created inside a stored procedure [vb6]

Question

I have a stored procedure inside which I create a temporary table. My vb code will execute this stored procedure.

Is it possible to access data in temp table in vb6 after sp is done?

Answer 1

Use a global region temporary table (they start with ## , not # ). They are shared between sessions. They go beyond when the session that created them ends, and no other session refers to them.

• Temporary tables in SQL Server

• Create and modify master tables

• SQL Server 2005 and Temporary Tablespace

Another option is a permanent temporary table (prefixed with TempDB.. )

Answer 2

From Books Online :

The local temporary table created in the stored procedure is deleted automatically when the stored procedure is completed. A table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced which called the stored procedure that created the table.

Answer 3

The temporary table created in the Stored Procedure is discarded when the stored procedure ends, so there is no answer.

If you really want to share the temporary table with the caller, you can do something like the following:

• Conditionally create a temporary table in a stored procedure, if it does not already exist

   CREATE PROCEDURE MyProcedure AS ... if object_id('tempdb..#temp') is null BEGIN CREATE TABLE #temp (...) END ... 

• Whenever you want to access the temp table from your caller, you must create a temporary table before calling the stored procedure.

By creating a temporary table conditionally in the Stored Procedure, it will work regardless of whether the calling program creates a temporary table. The caller must, of course, create a temporary table with the correct structure and DROP it upon completion (or close the connection to the database).

Answer 4

The option I used in the past is to create a temp table before calling the stored proc that uses it. As long as you use the same open adodb connection , it should work:

 myAdoDBConn.Execute "CREATE TABLE #foo (ID integer)", , adCmdText myADODBConn.Execute "StoredProcThatPopulatesFoo", , adCmdStoredProc myAdoRecordset.Open "Select ID FROM #foo", myAdoDbConn, adOpenForwardOnly, adLockReadOnly Do While Not myAdoRecordset.EOF // do something with the record // myAdoRecordset.EOF Loop 

In this example, the temp table remains available until you close the connection ( myAdoDbConn ).