How do you find the ID of the parent stored procedure within a nested procedure?

Question

If I am within a nested stored procedure, I would like to be able to get the procedure ID of the procedure that is one level up.
Example:

create proc procedure1
as
    print 'Current proc id: ' + @@PROCID
    print 'Parent proc id: ' + @@PROCID_PARENT --or something? 
go

create proc procedure2
as
    exec procedure1
go

exec procedure2

would give results something like:

Current proc id: 93440434
Parent proc id: 10022237

I have a stored procedure that might be executed from within several other stored procedures, and I'd like to be able to know which procedure is executing the child procedure. Is this possible?

Answer 1

Add a parameter to your child procedure and pass the parent @@PROCID...

create proc procedure1
@parentProcId int
as
    print 'Current proc id: ' + cast(@@PROCID as varchar)
    print 'Parent proc id: ' + cast(@parentProcID as varchar)
go

create proc procedure2
as
    exec procedure1 @@PROCID
go

exec procedure2

Answer 2

I don't believe there is such a function for Parent_ProcID. But you could try passing the Parent's Proc ID to the child procedure. OR you could create a global temp table in the parent proc and insert its proc_id into it. This would allow you to access the parents proc id from the global temp table

Global Temp Table Idea

create proc procedure1
as
    DECLARE @ParentProcID int;
    print 'Current proc id: ' + @@PROCID
    SET @ParentProcID = SELECT TOP 1 PROCID FROM ##myParentProcIDs
    print 'Parent proc id: ' + @ParentProcID
go

create proc procedure2
as
    Select 
    @@ProcID as PROCID into ##myParentProcIDs
    exec procedure1

    DROP TABLE ##myParentProcIDs
go

exec procedure2

Answer 3

There's no built in way to retrieve this. You'd need to pass the id, @@procid, or the name, object_name(@@procid), of the parent into the child as another parameter.

Answer 4

Sorry, there is no handy function.

• parameter
• SET CONTEXT_INFO
• ???

Answer 5

If you're tyring to debug a system, using SQL Profiler and tracking the SP:Starting and SP:Completed events may help figure things out. The NestLevel column migh help as well, particularly for filtering.