How to identify the calling stored proc

Question

I am hoping to add some loging to a particular stored proc that is cross called by about 5000 stored procs in 20 databases.

What I'd like to to is add to the top of the called stored proc something like:

insert into callLog values (@@caller, getdate())

So after a while I can get a nice list of all the stored procs that are calling this one.

Can anybody help me with the @@caller part?

Answer 1

Try

SELECT @caller = OBJECT_NAME(@@PROCID)

EDIT: After you got current proc. name, pass it to log sp as parameter, that's it.

This only works inside of stored procedure.

An example on using this

USE AdventureWorks;
GO
IF OBJECT_ID ( 'usp_FindName', 'P' ) IS NOT NULL 
DROP PROCEDURE usp_FindName;
GO
CREATE PROCEDURE usp_FindName
    @lastname varchar(40) = '%', 
    @firstname varchar(20) = '%'
AS
DECLARE @Count int;
DECLARE @ProcName nvarchar(128);
SELECT LastName, FirstName, Phone, EmailAddress
FROM Person.Contact 
WHERE FirstName LIKE @firstname AND LastName LIKE @lastname;
SET @Count = @@ROWCOUNT;
SET @ProcName = OBJECT_NAME(@@PROCID);
RAISERROR ('Stored procedure %s returned %d rows.', 16,10, @ProcName, @Count);
GO
EXECUTE dbo.usp_FindName 'P%', 'A%';

More on this

http://msdn.microsoft.com/en-us/library/ms174408(SQL.90).aspx

An example on stored procedure logging. Can be useful

http://weblogs.sqlteam.com/brettk/archive/2006/09/21/12391.aspx

Here's a related question also

http://stackoverflow.com/questions/713677/can-a-ms-sql-stored-procedure-look-up-its-own-name

EDIT 2: and other related question in SO

http://stackoverflow.com/questions/148004/find-out-the-calling-stored-procedure-in-sql-server

Answer 2

You could potentially identify the original requests coming into the server, using DBCC INPUTBUFFER, but I don't think there's anything that allows you to find the immediate Caller of a proc.

(Quickly cooked up example - may be various permissions issues here, may want to move the logging portion into a different stored proc with different EXECUTE AS settings):

create table CallLog (
    EventType varchar(50) not null,
    Parameters int not null,
    EventInfo varchar(max) not null
)
go
create procedure Callee
as
    declare @Logger varchar(1000)
    set @Logger = 'dbcc inputbuffer(' + CONVERT(varchar(10),@@SPID) + ')'
    insert into CallLog(EventType,Parameters,EventInfo)
    exec(@Logger)

    --Now do the actual work for the stored proc
    select * from sysobjects
go
create procedure Caller
as
    exec Callee
go
exec Caller
go
select * from CallLog

Answer 3

Unfortunately, there is no way to access SP call stack from T-SQL. If you need it temporarily for debugging purposes, you could use SQL Profiler.

This article describes a workaround - basically, implementing your own call stack using SET CONTEXT_INFO.
If you need it just for a single procedure, though, it seems more convenient just to add an extra parameter.