Home Forums SQL Server 2008 T-SQL (SS2K8) Passing temp table from one stored procedure to another RE: Passing temp table from one stored procedure to another

  • If a Procedure calls another procedure INSIDE it's code, any #temp tables are available to it that exist in the procedure;

    if the procedures are called consecutively, then the temp table has to exist prior to the first procs call, or if the proc will create the table, then only a global temp table(##Temp) will be available in that scope.

    so if the proc is called inside

    CREATE PROCEDURE Example

    AS

    BEGIN

    -- Create the temp table

    CREATE TABLE #temp (tempid int, temptext varchar(30))

    --populate it

    INSERT INTO #temp

    SELECT * FROM [SomeAnonymousTable)

    --call a child procedure that might do something witht eh temp table

    EXECUTE ExampleProcTwo

    END --PROC

    GO

    EXECUTE Example

    CREATE PROCEDURE Example

    AS

    BEGIN

    -- Create the global temp table

    CREATE TABLE ##temp (tempid int, temptext varchar(30))

    --populate it

    INSERT INTO ##temp

    SELECT * FROM [SomeAnonymousTable)

    END --PROC

    GO

    EXECUTE Example

    EXECUTE ExampleProcTwo

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!