What is scope of temp table when called by nested stored procedure?

Question

This is two part question:

1. I have two stored procedures: sp1 & sp2. If sp1 creates a temp table #temp then executes sp2 will I have access to #temp in the nested procedure? If not, how to do it in another way?

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

Answer 1

1. Yes, the temp table is in the scope of the connection, so the nested stored procedure (sp2) will have access to #temp table create in sp1.

2. Yes, in SQL 2008 we have ability to pass a table valued parameter (TVP) as input to a function or stored procedure. You can read more here.

Answer 2

IT WORKS , to access temp table in child procedures , it must be declared in 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 accessible in child SP. And it gives below output.

100