What is scope of temp table when called by nested stored procedure?
sql-server, stored-procedures, t-sql
Solution
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.
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.
Problem
This is two part question: 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? 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.