查找SQL Server中调用的存储过程

Is it possible to find out who called a store procedure?

有可能找出是谁调用了存储过程吗?

For example, say I get an error in proc3. From within that proc I want to know if it was called by proc1 or proc2.

例如，我在proc3中得到一个错误。从这个proc中，我想知道它是由proc1还是proc2调用的。

4 个解决方案

#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.

这也将使将数据库移植到另一个平台更容易，因为您不需要依赖于某个模糊的平台相关函数。

#2

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

您需要在proc3中知道是什么在运行时导致了错误，还是只需要在调试时知道?

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

如果您只需要在调试/监视期间执行，您可以使用SQL Server profiler。

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

否则在2005年，我不相信您有能力堆栈跟踪。

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

要解决这个问题，可以向proc3、@CallingProc或类似的东西添加额外的参数。

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

或者可以向proc1和proc2添加try catch块。

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

这里有很好的参考:http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1189087,00.html

and of course always SQL Server Books Online

当然，总是SQL Server在线图书

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

但是，SQL Server 2008确实能够通过过程进行调试。

#3

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?

这对递归调用不起作用，但是也许有人可以修复它?

#4

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

您可以将proc1和proc2的名称作为参数传递给proc3。

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

#1