How to determine the called stored procedure

Question

I hope to add some entry to a specific saved process, which is cross-named about 5000 stored procs in 20 databases.

I would like to add something like the top of the called stored proc:

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

So, after a while I can get a good list of all stored processes that cause this.

Can someone help me with @@ member?

Answer 1

Try

SELECT @caller = OBJECT_NAME(@@PROCID)

EDIT: After you got the current procedure. name, pass it to sp log as parameter to it.

This only works inside the stored procedure.

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%';

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

.

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

MS/Transact-SQL ?

EDIT 2: SO

SQL Server

Answer 2

, , DBCC INPUTBUFFER, , -, proc.

( - , EXECUTE AS):

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 the SP call glass from T-SQL. If you need it temporarily for debugging purposes, you can use SQL Profiler.

This article describes a workaround - basically, implementing your own call stack using SET CONTEXT_INFO .
However, if you need it for only one procedure, it seems more convenient to add an additional parameter.