Can a SQL Server stored proc determine its parent proc's name?

Question

If Proc A executes Proc B, is there a way for Proc B to look-up that it was called by A instead of having a parameter where A passes B its ID?

Per request: The reason I'm interested in this is multi-fold 1) General knowledge, I'm sure if it can be done it would involve clever use of some system tables/variables that may help me do other things down the road.

2) As others have mentioned, logging/auditing. I'd like to make a procedure that logs a begin, end and message entry that requires no parameters, and accepts one optional parameter of a user specified message. This would allow one to simply drop an exec in the top and bottom of a proc to make it work and the audit procedure would figure out the rest on its own.

I know this info is available in the log files, but parsing those and giving them to users is not all that straight forward, whereas, this would give easy access to that basic info.

3) Used in conjunction with a semaphore such a generalized procedure could ensure that related processes are not executed simultaneously regardless of sessions/transactions etc.

Answer 1

Why would you want to do that?
AFAIK, there is no way for proc B to know who called it.

EDIT: As KM shows that it is possible (as per the code), I am interested in understanding the reason behind doing this. Can you post that as well, by adding it to your question?

Answer 2

If a stored procedure needs to behave differently based on who calls it, then it needs to add a parameter. That way, if you add stored procedure "Z", the code will still work - "Z" can pass the parameter the way that "C" passed it, or the way "D" passed it. If that's not good enough, then new logic needs to be added in "B".

Answer 3

no, it cant tell what sproc called it. You'd need to add an extra parameter in to tell it who the caller is

Answer 4

use a prarameter like this:

CREATE PROCEDURE ParentProcedure

AS

DECLARE @ProcID             int

SET @ProcID=@@PROCID

EXEC ChildProcedure @ProcID

RETURN 0
go

and this...

CREATE PROCEDURE ChildProcedure
(
@ProcID   int=null --optional
)
AS

if @ProcID IS NOT NULL
BEGIN
    PRINT 'called by '+OBJECT_NAME(@ProcID)
END

RETURN 0
go

Answer 5

In MSSQL Server 2008 you can use sys.dm_exec_procedures_stats, this dynamic management view can show you when stored procedure (see also sys.procedures to obtain name of the procedure) was executed and so on.

SELECT s.*, d.*
FROM sys.procedures s
INNER JOIN sys.dm_exec_procedure_stats d
ON s.object_id = d.object_id
ORDER BY [d.last_execution_time] DESC;

Parent-procedure will be shown in this result set very close, because this procedure will be executed earlier. Of course, this is not complete solution of your problem, but you may obtain some info.

and yes, if there is concurrency, this solution doesn't works. It can help in development or debug only.