locked
Reference a Stored Procedure from another Stored Procedure. RRS feed

  • Question

  • Hello. I am new to the forum system... go easy on me :)

    I am working in SQL Server Management Studio 2008. I have a stored procedure, say st_alpha and a second stored procedure, say st_beta . If I execute st_alpha , and within it, st_beta is executed, then st_beta writes to a log, is there a way to write in the log which stored procedure executed st_beta ? St_alpha could be any stored procedure in our system, st_beta on the other hand, is the same stored procedure that could be executed from within any st_alpha. I would just like to write a record to the log from st_beta as to which stored procedure executed st_beta without having to go through all of the st_alpha procedures and add a description variable that is passed to st_beta.

     

    Any ideas?

    Monday, January 10, 2011 5:52 PM

Answers

All replies

  • There is no simple way, if there is one, to have access to the call stack from T-SQL. If you think that this will be a useful feature to have in SQL Server, then feel free to vote on this suggestion made by Erland Sommarskog.

    Provide function to retrieve the entire call stack

     


    AMB

    Some guidelines for posting questions...

    Monday, January 10, 2011 6:06 PM
  • Check this thread However, I remember another way and I think I saw it recently in one of Peter Larsson's post. I searched and I could not find it right now - may be I didn't recall the originator of the idea correctly? 

    See also this suggestion (using @@SPID)


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, January 10, 2011 6:15 PM
  • Thanks for the recommendation, I'll see if I can get @@SPID to work.
    Monday, January 10, 2011 7:47 PM
  • Found also this blog post (could it be this is what I meant? - doubtfully)

    http://beyondrelational.com/blogs/madhivanan/archive/2010/09/06/findout-calling-procedure-name-using-procid.aspx


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Kalman Toth Tuesday, January 18, 2011 3:56 PM
    Monday, January 10, 2011 7:57 PM
  • The only way to do this is to pass @@procid as a parameter to the called procedure. The procedure can then use object_name(@@procid) to get the name of the procedure.

    We use this in quite a few places.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Proposed as answer by HunchbackMVP Tuesday, January 11, 2011 3:48 PM
    • Marked as answer by KJian_ Tuesday, January 18, 2011 9:41 AM
    Monday, January 10, 2011 10:01 PM
  • That's what I ended up doing and it worked great. Thanks for the replies everyone!!
    Tuesday, January 11, 2011 1:08 PM