What is a temporary table area when calling a nested stored procedure?

Question

These are two questions:

• I have two stored procedures: sp1 and sp2. If sp1 creates a temporary #temp table, then sp2 is executed, will I have access to #temp in a nested procedure? If not, how to do it differently?

• Can a function accept a type table parameter? I tried, but SQL Server gave me an error. Why can't this work? Maybe sqlserver should support something like Generic.

Answer 1

• Yes, the temporary table is in the connection area, so the nested stored procedure (sp2) will have access to the #temp create table in sp1.

• Yes, in SQL 2008, we have the ability to pass a table parameter (TVP) as an input to a function or stored procedure. You can read further here .

Answer 2

IT WORKS, to access the temporary table in child procedures, it must be declared in the parent.

create proc test2 As BEGIN insert into #tmpchild select 100 END CREATE PROC [dbo].[TEST] As BEGIN create table #tmpchild(id int) exec test2; select * FROM #tmpchild; END 

Here, when you run TEST SP, it calls test2, #tempchild is available in the child SP. And he gives below exit.

 100 

Answer 3

This example works:

A temporary procedure table is available in a nested procedure.

 alter procedure tests2 (@tmptbl varchar(20)) as declare @sql as nvarchar(1000) set @sql = 'select * from ' + @tmptbl exec sp_executesql @sql print @tmptbl go alter procedure tests1 as IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tmpchild')) BEGIN DROP TABLE #tmpchild END create table #tmpchild(id int) insert into #tmpchild(id) values(100) exec tests2 '#tmpchild' go exec tests1 

Answer 4

Access to the Temparary Table can be obtained in the child SP when called from Parent SP, since the temporary table is in the connection area. See below code for reference.

 CREATE PROCEDURE ParentSP AS BEGIN --Assuming, there is a pre-existing 'Employee' table in DB select * INTO #TempTable FROM Employee --temparary table created here EXEC ChildSP END GO CREATE PROCEDURE ChildSP AS BEGIN (select COUNT(1) as Count FROM #Temp) select * FROM #TempTable --tempary table assessed here END GO EXEC ParentSP