SQL 找出SQL Server中调用的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/148004/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find out the calling stored procedure in SQL Server
提问by Craig HB
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 proc1or proc2.
例如，假设我在proc3. 在那个过程中，我想知道它是否由proc1或调用proc2。
采纳答案by GvS
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.
这也将使将您的数据库移植到另一个平台变得更加容易，因为您不依赖于某些晦涩的平台相关功能。
回答by evilhomer
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 profilerif you only need to do it during debugging/monitoring.
如果您只需要在调试/监视期间执行此操作，则可以使用SQL Server 探查器。
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.
或者您可以将 try catch 块添加到 proc1 和 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
这里很好的参考：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 确实具有通过过程进行调试的能力。
回答by AJ.
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.
没有很好的自动方法来做到这一点（唉）。因此，这实际上取决于您准备（重新）编写 procs 以便能够做到这一点的程度。
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?
这不适用于递归调用，但也许有人可以解决这个问题？
回答by Kramii
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
