Find Parent for a Proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Find Parent for a Proc

Author

Topic

skond
Yak Posting Veteran

55 Posts

Posted - 2002-08-20 : 12:58:38

Is there a function or system proc which would give me the procedure that called another procedure.

eg:
proc1 calls proc2

In my code for proc2 can I call a system proc to know that proc1 called proc2.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-20 : 13:12:56

You can use @@NESTLEVEL to tell whether the 2nd proc was called by another proc or directly, but not the name of the calling proc. The @@PROCID variable can provide the object ID of the currently executing procedure; you can use it as a parameter to pass to the 2nd procedure when being called and then utilize it. You can also use sp_depends to determine which procedures depend on other procedures, but it won't return which one called another SP when it's run.

Forgive me for asking, but how do you NOT know which procedure called another? You did write the code, correct? SPs don't just call other SPs of their own volition. Are you troubleshooting a procedure?

skond
Yak Posting Veteran

55 Posts

Posted - 2002-08-20 : 13:42:20

I'm trying to create a process for tracking when different procedures were run, the parameters passed, start time, end time and also the proc taht invoked it. Sometimes a procedure will be called as part of a big process and sometimes called seperately. So to populate the column parent_proc that I'm maintaining I cannot hard code the value. But your reply to use @@nestlevel could help.

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-20 : 13:56:57

Actually, I played around a little bit and found that this works pretty well:

CREATE PROCEDURE procTest @procID int=Null AS
SELECT @procID=IsNull(@procID, @@PROCID)
SELECT 'This procedure was called by ' + CAST(name as varchar)
FROM sysobjects
WHERE ID=@procid

If you call it like this:

EXECUTE procTest @@PROCID

It will display the calling procedure. If you don't pass anything it will assume it was called directly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources