Call Stack Level

I figure there is someway to pull this out but I can't find it anywhere. I
have a recursive stored procedure and I want to error out if it recurses
more than 10 times. How can I pull out how deep in the call stack I am? Is
this possible? I know I could pass a parameter to the stored procedure and
keep incrementing it but I was hoping there was a better way.
--
TIA
AltmanAltman (NotGiven@.SickOfSpam.com) writes:
> I figure there is someway to pull this out but I can't find it anywhere.
> I have a recursive stored procedure and I want to error out if it
> recurses more than 10 times. How can I pull out how deep in the call
> stack I am? Is this possible? I know I could pass a parameter to the
> stored procedure and keep incrementing it but I was hoping there was a
> better way.
There is a global variable (or function as Microsoft calls it these
days), @.@.nestlevel that holds this information. @.@.nestlevel is increased
by 1 for every scope you to descend to. For this reason, I would be hesitant
to use @.@.nestlevel, as the rules would change if your procedure was
wrapped into another procedure. Passing a parameter is probably better.
The max nesting level in SQL Server is 32, by the way.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx