CODEWITHSUNDEEP
sqlsql-server
Cristian Abelleira
Cristian Abelleira

Reputation: 355

Access #TempTable declared in child procedure in parent procedure

I have nested stored procedures and I need to create a LOCAL TEMP TABLE in child procedure and be able to use it in the parent procedure.

EX:

Parent procedure:

EXEC ChildProcedure
SELECT * FROM #TempTable

Child procedure:

CREATE TABLE #TempTable (Field1 VARCHAR(1000),Field2 VARCHAR(1000))
INSERT INTO #TempTable (Field1,Field2) VALUES ('1','2')

When I try this, SQL says:

Invalid Object Name '#TempTable'

Is there any way to achieve this without GLOBAL TEMP TABLES ?

Upvotes: 1

Views: 2080

Answers (2)

Cristian Abelleira
Cristian Abelleira

Reputation: 355

Well I think I finally found the answer to my question in https://msdn.microsoft.com/en-us/library/ms174979.aspx.

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

So the answer to my own question is NO. I can't do that in that way.

The best approach (as @Damien_The_Unbeliever said) is to create the table in the parent procedure and populate it inside the child procedure.

Upvotes: 4

Arun Gairola
Arun Gairola

Reputation: 884

Instead of Procedure you can create a table valued function like this and use a 

Create Function ChiledFunction ()
Returns  @TempTable Table  (Field1 VARCHAR(1000),Field2 VARCHAR(1000)) AS 
Begin
INSERT INTO @TempTable (Field1,Field2) VALUES ('1','2')
Return 
end

and parent procedure is here 

Create Procedure ParentProc 
As
 begin 
   select * from dbo.ChiledFunction()

end

Upvotes: 0

Related Questions