Find out the calling stored procedure in SQL Server

Question

Is it possible to find out who called a store procedure. So if you get an error in proc3, I know if it was called by proc1 or proc2.

I'm using SQL Server 2005.

Answer 1

I would use an extra input parameter, to specify the source, if this is important for your logic.

This will also make it easier to port your database to another platform, since you don't depend on some obscure platform dependent function.

Answer 2

Do you need to know in proc3 at runtime which caused the error, or do you just need to know while debugging?

You can use SQL Server profiler if you only need to do it during debugging/monitoring.

Otherwise in 2005 I don't believe you have the ability to stack trace.

To work around it you could add and extra parameter to proc3, @CallingProc or something like that.

OR you could add try catch blocks to proc1 and proc2.

BEGIN TRY
EXEC Proc3
END TRY
BEGIN CATCH
SELECT 'Error Caught'
SELECT
    ERROR_PROCEDURE()
END CATCH

Good reference here : http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1189087,00.html

and of course always SQL Server Books Online

SQL Server 2008 does have the ability to debug through procedures however.

Answer 3

You could have proc1 and proc2 pass their names into proc3 as a parameter.

For example:

CREATE PROCEDURE proc3
  @Caller nvarchar(128) -- Name of calling proc.
  AS
  BEGIN
    -- Produce error message that includes caller's name.
    RAISERROR ('Caller was %s.', 16,10, @Caller);
  END
  GO

  CREATE PROCEDURE proc1
  AS
  BEGIN
    -- Get the name of this proc.
    DECLARE @ProcName nvarchar(128);
    SET @ProcName = OBJECT_NAME(@@PROCID);
    -- Pass it to proc3.
    EXEC proc3 @ProcName
  END
  GO

  CREATE PROCEDURE proc2
  AS
  BEGIN
    -- Get the name of this proc.
    DECLARE @ProcName nvarchar(128);
    SET @ProcName = OBJECT_NAME(@@PROCID);
    -- Pass it to proc3.
    EXEC proc3 @ProcName
  END
  GO

Answer 4

There is no nice automatic way to do this (alas). So it really depends on how much you are prepared to (re)write your procs in order to be able to do this.

If you have a logging mechanism, you might be able to read the log and work out who called you.

For example, if you implement logging by inserting to a table, for example:

CREATE TABLE Log
(timestamp dattime, 
spid       int, 
procname   varchar(255), 
message    varchar(255) )

... text of proc ... 
INSERT INTO Log
SELECT get_date(), @@spid, @currentproc, 'doing something' 
-- you have to define @currentproc in each proc

-- get name of caller
SELECT @caller = procname 
FROM   Log
WHERE  spid = @@spid 
AND    timestamp = (SELECT max(timestamp) 
                    FROM   Log 
                    WHERE  timestamp < get_date() 
                    AND    procname != @currentproc )

This wouldn't work for recursive calls, but perhaps someone can fix that?

Answer 5

Okay, again, for future googlers, you can know the name of the current SP by getting

OBJECT_NAME(@@PROCID)