SQL Server Performance

parameter to know the deapth of stored procedure

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Jan 28, 2010.

  1. shankbond New Member

    Hi,
    A Basic Question?
    Is there a parameter to know the depth of the calling procedure; just like @@TRANCOUNT which reports the open transactions, is there a way to know that the current running procedure was nested or not?
    Like Procedure A calling B, then is there a way that B Know that it was called from Procedure A; not executed directly.
    I hope You understand What I akm trying To tell.
    Please Reply
  2. patel_mayur New Member

    You can use sp_depends <your_procedure_name>
    OR
    You can view dependencies using Object Dependencies from Query Analyzer.
  3. preethi Member

    [quote user="patel_mayur"]
    You can use sp_depends <your_procedure_name>
    OR
    You can view dependencies using Object Dependencies from Query Analyzer.
    [/quote]
    This will give only the dependencies of the said procedure. It will not give any ideas on whether procedures are called directly or by another procedure.
    There could be a better method, but I can think of using profiler to catch sp starting and sp completed so that you can see if a procedure is called by another procedure.

  4. patel_mayur New Member

    Oh... i have misinterpreted the question.
    I believe there is no system variable available to let user know whether the procedure is called directly or by another procedure.
    Alternative could be, you can put signature/flag in calling procedure before make a call to another procedure.
  5. FrankKalis Moderator

    Maybe @@NESTLEVEL gives you what you want.
  6. shankbond New Member

    Thanks for the help all of You Specially FrankKalis
    [Y]
  7. patel_mayur New Member

    Oh... i have misinterpreted the question.
    I believe there is no system variable available to let user know whether the procedure is called directly or by another procedure.
    Alternative could be, you can put signature/flag in calling procedure before making a call to another procedure.

Share This Page