Script for finding the Sub Level of Procedure

• VamsiKrishna.Musunuru

  SSC Enthusiast

  Points: 132

  More actions

  May 31, 2007 at 1:08 am

  #63075

  Hi,

  I have a Stored Procedure which in turn calls another Procedure and which in Turn calls another procedures ..... The Problem is Level of Calling the Procedure is Exceeding 32 (Max Level in SQL Server 2000).

  Are there any tools or Scripts to find Out the Sub Level Calls or to find the Proc which is Exceeding the Max Level.

  I tried to find out Using SQL Profiler (2000). It's only showing the First Calling Procedure...but not the Sub Levels....

  Thanks in advance for your Help....

• VamsiKrishna.Musunuru

  SSC Enthusiast

  Points: 132

  More actions

  May 31, 2007 at 4:10 am

  #709944

  I got it. We have KeyWord "@@NESTLEVEL" to find out the level of the Procedure.

  For Exapmle:

  CREATE PROCEDURE innerproc as

  select @@NESTLEVEL AS 'Inner Level'

  GO

  CREATE PROCEDURE outerproc as

  select @@NESTLEVEL AS 'Outer Level'

  EXEC innerproc

  GO

  EXECUTE outerproc

  GO

  Here is the result set:

  Outer Level

  -----------------

  1

  Inner Level

  -----------------

  2