Is there a way to pass temporary tables across the stored procedures

Question

I have 4 stored procedures. I need to take the result of the first stored procedure (2 temp tables) and pass it into the second stored procedure. These temp tables need to be used in the from clause in the second stored procedure.

Similarity the third and fourth stored procedures need results from the previous stored procedures. is there a way to pass temporary tables across the stored procedures?

it was 1 Sp but we broke it into 4 so its easier to alter if needed — Mwright, Sep 25 '14 at 15:32
@Mwright, is this SQL server or oracle, my example works fine on sql server, but not sure about oracle. — radar, Sep 25 '14 at 16:04
Does this answer your question? [How to pass a temp table as a parameter into a separate stored procedure](https://stackoverflow.com/questions/20105443/how-to-pass-a-temp-table-as-a-parameter-into-a-separate-stored-procedure) — Michael Freidgeim, May 20 '22 at 07:21

score 2 · Accepted Answer · answered Sep 25 '14 at 15:51

Regarding this comment, "it was 1 Sp but we broke it into 4 so its easier to alter if needed", I suggest that you break it up even more. In other words, implement encapsulation.

Have a separate stored procedure for each time you want to select data from the actual tables. Do not populate temp tables in these procedures, just return the data.

Then write a stored procedure that creates and populates temp tables from the procs mentioned above, and does the necessary processing.

Here is a simple example:

create procedure GetData1
select Field1, Field2
from blah, blah, blah

create procedure AssembleAllData
create table #temp1 (Field1, Field2)
insert into #temp1
exec GetData1
select Field1, Field2, etc
from #temp1 join anActualTable etc

drop table #temp1

Just spent the past hour trying to figure out how to populate a temp table from a SP, I didn't even consider that I only need my big and tidy result formatting for the final result, so I can call the procedure for returning that where needed, and have the temporary variables in whatever messy format is needed inside the procedure. Thanks for the suggestion! — Aberrant, Mar 28 '19 at 14:29

score 0 · Answer 2 · answered Sep 25 '14 at 14:49

In your current SP1, you can create temporary table pass the name to the second stored procedure like below

SP1 code

IF OBJECT_ID('tempdb.dbo.#TempTable1')  IS NOT NULL 
        DROP TABLE #TempTable1
EXEC SP2  N'#TempTable1'

Inside the SP2 you can insert the values into #TempTable1 which will be available to the calling SP

SP2 code

CREATE procedure [dbo].[SP2]
  @outTempTable NVARCHAR(128)
AS

  IF @outTempTable IS NOT NULL AND LEN(@outTempTable) > 0 
  BEGIN
        EXEC (  'INSERT INTO ' + @outTempTable + ' SELECT  * FROM TableA' )
  END

score 0 · Answer 3 · answered Sep 25 '14 at 15:28

Your question sounds more like an answer than a question. Just do as you described.

You don't need to pass the data in the temp tables from one procedure to the next. The data is just there. In one procedure you write to the temp table and in the next procedure you read from the temp table.

I would also not create temp tables dynamically, just create them and let them wait for data. This assumes that the temp table data is local to a session (in oracle this is the case and in a way the reason why temp tables exist).

Also I would opt against passing table names between procedures. There is almost always a better way and it is a no-no anyways. If you are under the impression that you need variable temp table names, then you really want to add another column to the temp tables (you may even call it "temp_table_name", though it almost certainly means something different). Then you can pass the "temp_table_name" around and the selects would need a where temp_table_name = ... and the inserts would have to populate this extra column.

I'm not sure that this would work. My understanding is that once the proc that populates the temp tables is finished executing, the temp table goes away. I'd have to try it to be sure though. — Dan Bracuk, Sep 25 '14 at 15:39
In oracle it does not and would be surpised if other RDBMSs do things differently. The data may be deleted on commit or stay until explicitly deleted (depends on how they are created). I am assuming you really mean temp tables and not some data structure in your code. — Martin Drautzburg, Sep 25 '14 at 15:42

Is there a way to pass temporary tables across the stored procedures

3 Answers3

Linked