How to Pass a Temp Table as a Parameter into a Separate Stored Procedure

How to pass a temp table as a parameter into a separate stored procedure

When you create a #TEMP table, the "scope" is bigger than just the procedure it is created in.

Below is a sample:

IF EXISTS 
    (
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspProc002'  
    )
BEGIN
    DROP PROCEDURE [dbo].[uspProc002]
END

GO

CREATE Procedure dbo.uspProc002 
AS

BEGIN

/* Uncomment this code if you want to be more explicit about bad "wiring" */
/*
IF OBJECT_ID('tempdb..#TableOne') IS NULL
begin
        THROW 51000, 'The procedure expects a temp table named #TableOne to already exist.', 1;  
end
*/

    /* Note, I did not Create #TableOne in this procedure.  It "pre-existed".  An if check will ensure that it is there.  */
    IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
    begin
        Insert into #TableOne ( SurrogateKey , NameOf ) select 2001, 'Hello-From-uspProc002'
    end

END

GO

IF EXISTS 
    (
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspProc001'  
    )
BEGIN
    DROP PROCEDURE [dbo].[uspProc001]
END

GO

CREATE Procedure dbo.uspProc001 (
@Param1 int
)
AS

BEGIN

    IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
    begin
            drop table #TableOne
    end

    CREATE TABLE #TableOne
    ( 
    SurrogateKey int , 
    NameOf varchar(12)
    )

    Insert into #TableOne ( SurrogateKey , NameOf ) select 1001, 'Hello-From-uspProc001'

    Select 'before-nested-call' as MyStatus1, * from #TableOne

    EXEC dbo.uspProc002 

    Select 'after-nested-call' as MyStatus1, * from #TableOne

    IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
    begin
            drop table #TableOne
    end

END

GO

exec dbo.uspProc001 0

HAVING SAID THAT, PLEASE DO NOT CODE UP ALOT OF THESE. ITS THE SQL EQUIVALENT OF A GLOBAL VARIABLE AND IT IS DIFFICULT TO MAINTAIN AND BUG PRONE.

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

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

How to access Temp Table in the stored procedure which is created in another stored procedure?

Building on Damien comments..

The table can be referenced by any nested stored procedures executed by the stored procedure that created the table

create proc usp_innertest1
as 
begin
select n as innertest1 from #test
end

create proc usp_innertest2
as 
begin
select n as innertest2 from #test
end

create proc dbo.test
as
begin
select top 1* into #test from numbers
exec usp_innertest1
exec usp_innertest2
end

now Executing test gives

innertest1 

1

innertest2

1

The table cannot be referenced by the process which called the stored procedure that created the table

this is obvious,if usp_innertest1 creates a temp table,it can't be accessed by test(main calling process)

There are also global temporary tables which reside till all the references are closed

---connection1

select top 1 * into ##test from numbers

--now open new connection(connection 2) and do below

begin tran

update ##test
set id=1

--now close connection1

-- now go to connection 2
select * from ##test

you can access this table until you committed it

commit

---

Related Topics