Max degree of recursion

Hi Everyone
As I understand it the maximum call stack level of recursive stored
procedures is 32. Is there at way around this limitation?
I want to make a procedure that traverses a tree structure in my database to
discover if a given node is above the input parameter. Ex:
CREATE TABLE Nodes
(
NodeID int,
ParentNodeID int
)
DECLARE @.MyNodeID int
DECLARE @.ParentNodeID int
DECLARE @.IsParent bit
SET @.MyNodeID = 42
SET @.ParentNodeID = 13
EXEC spIs_Parent @.MyNode, @.ParentNodeID, Is
... What is the best way to implement such a procedure?
Yours sincerely
Thomas Kejser
M.Sc, MCDBA
Do it the sloppy way without using recursion.
Do you have the code?
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)

Max degree of recursion

Hi Everyone
As I understand it the maximum call stack level of recursive stored
procedures is 32. Is there at way around this limitation?
I want to make a procedure that traverses a tree structure in my database to
discover if a given node is above the input parameter. Ex:
CREATE TABLE Nodes
(
NodeID int,
ParentNodeID int
)
DECLARE @.MyNodeID int
DECLARE @.ParentNodeID int
DECLARE @.IsParent bit
SET @.MyNodeID = 42
SET @.ParentNodeID = 13
EXEC spIs_Parent @.MyNode, @.ParentNodeID, Is
... What is the best way to implement such a procedure?
Yours sincerely
Thomas Kejser
M.Sc, MCDBADo it the sloppy way without using recursion.
Do you have the code?
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)

Max degree of recursion

Hi Everyone
As I understand it the maximum call stack level of recursive stored
procedures is 32. Is there at way around this limitation?
I want to make a procedure that traverses a tree structure in my database to
discover if a given node is above the input parameter. Ex:
CREATE TABLE Nodes
(
NodeID int,
ParentNodeID int
)
DECLARE @.MyNodeID int
DECLARE @.ParentNodeID int
DECLARE @.IsParent bit
SET @.MyNodeID = 42
SET @.ParentNodeID = 13
EXEC spIs_Parent @.MyNode, @.ParentNodeID, Is
... What is the best way to implement such a procedure?
--
Yours sincerely
Thomas Kejser
M.Sc, MCDBADo it the sloppy way without using recursion.
Do you have the code?
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)