You can use SQL Profiler to find out by which procedure the second procedure
is called if you trace the SQL:StmtStarted or SQL:StmtCompleted events.
If you trace the following:
CREATE PROC usp_inner
AS
SELECT 1
GO
CREATE PROC usp_outer
AS
EXEC usp_inner
GO
EXEC usp_outer
GO
DROP PROC usp_outer, usp_inner
GO
You will see
-- usp_outer
EXEC usp_inner
-- usp_inner
SELECT 1
in the TextData column of your trace. The commented out bit is the calling
procedure. Even when you execute a variable procedure name, you can still
find out the calling procedure, because the calling procedure name will be
in the event before the first event that includes the called procedure's
name (like the example above)
--
Jacco Schalkwijk
SQL Server MVP
> Hi,
> Is there any way to get an object ID for the the stored procedure or
trigger
> that called the currently executing stored proc?
executing
> or trigCallingTrig .. then I want the object id for that calling proc /
> trigger.
> This is all for debugging something on our system.
> Thanks
> Louis