CODEWITHSUNDEEP
sqlstored-procedures
Mwright
Mwright

Reputation: 7

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

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?

Upvotes: 0

Views: 6955

Answers (3)

Dan Bracuk
Dan Bracuk

Reputation: 20794

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

Upvotes: 2

Martin Drautzburg
Martin Drautzburg

Reputation: 5243

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.

Upvotes: 0

radar
radar

Reputation: 13425

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

Upvotes: 0

Related Questions