CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
SleeplessKnight
SleeplessKnight

Reputation: 2185

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

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.

Upvotes: 9

Views: 14485

Answers (4)

Jigar Parekh
Jigar Parekh

Reputation: 625

It works to access a 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

When you run the TEST SP, which calls test2, #tempchild is accessible in the child SP (test2).

And it gives below output:

100

Upvotes: 14

Nihir Mandowara
Nihir Mandowara

Reputation: 63

Temparary Table can be accessed in the Child SP when called from Parent SP as temp table is in the scope of the connection. Please 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

Upvotes: 1

Ashish T.
Ashish T.

Reputation: 11

This example works :

Temp table of a procedure is accessible in 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

Upvotes: 1

nathan_jr
nathan_jr

Reputation: 9282

  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.

Upvotes: 13

Related Questions