So I've got multiple stored procedures that call another stored procedure. I need to know which stored procedure did the calling when it happens.
To clarify, let's say that Sproc1 and Sproc2 both sometimes call Sproc3. Whenever Sproc3 is called, I want it to do something special if it was called by Sproc2, but not if it was called by Sproc1, so I need to know which stored procedure called Sproc3 each time it's called.
The obvious solution is to have Sproc1 and/or Sproc2 pass a variable to Sproc3 saying who the caller was. Unfortunately, Sproc1 and Sproc2 are both written by another company that's a pain in the &^% to work with. We're not allowed to modify their sprocs, and we don't have the time and money to invest in having them do it.
So is there a way within Sproc3 to know what stored procedure called it?
There is nothing built into SQL Server that I know of that will give you the type of call-stack information that you are looking for. There are hacks/workarounds, but all of those require modifying the calling stored procedure or wrapping the calling stored procedure in another stored procedure and then calling the wrapper.
Yeah, I'm getting that impression from googling for solutions. Nobody has a way to do this, so it's probably not possible.
Thanks anyway. We've got enough other things going on around these procedures that we should be able to come up with some other hack to do what we need, but I already know it won't be pretty.
The request was made to Microsoft to provide the ability to view call stack via a connect issue quite some time ago, and Microsoft agreed that it would be a useful feature, and that they would consider implementing it in the next version.
But that was, like seven versions ago and nothing has been done yet.
For most situations, you could probably get by using the last execution of the calling proc based on this view:
sys.dm_exec_procedure_stats
Presumably the calling stored proc will be in that view (you can test for that first in your called procs, to check if they 'see' the calling proc in that view when the child proc starts).
For now, I'll assume both procs are in the same db, but the code could be adjusted to handle the caller being in a different db, as long as you know which one(s) it is(could be).
SELECT TOP (1) OBJECT_NAME(object_id)
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID() AND OBJECT_NAME(object_id) IN ('sproc1', 'sproc2')
ORDER BY last_execution_time DESC
