ERROR:"Maximum stored procedure function, "

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ERROR:"Maximum stored procedure function, "

Author

Topic

raniel
Starting Member

3 Posts

Posted - 2004-12-16 : 03:37:30

an error error occurs when i try to execute this function. well this actually works,
but if i have a few hundred deep hierarchy or loops. (ex. 97 deep)

"Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."

tblPositioning table

f_SlotID | f_PrntSlotID
========================
1 0
2 1
3 1
4 2
5 2
6 3
7 3

*f_SlotID = CHILD
*f_PrntSlotID = PARENT

Any Help?? Thanks

this is my function

CREATE Function getTreeMANO(@ID Int,@Level int=1)
--Returns @Tree Table(xLevel int,xID int,xName Varchar(10),xParentID int)
Returns @Tree Table(xLevel int,f_SlotID int,f_PayType Varchar(10),f_PrntSlotID int)
As
Begin
Declare @tID int,@tName Varchar(10),@tParentID int
Declare TreeCur Cursor
For Select f_SlotID, f_paymentType,f_PrntSlotID From tblpositioning Where f_PrntSlotID=@ID --For Select * From Tree Where ParentID=@ID
Open TreeCur
FETCH NEXT FROM TreeCur into @tID,@tName,@tParentID

Insert Into @Tree Select @Level, f_SlotID, f_paymentType,f_PrntSlotID From tblpositioning Where f_SlotID=@ID
Set @Level=@Level+1
WHILE (@@fetch_status = 0)
Begin
Insert Into @Tree Select * From dbo.getTreeMANO(@tID,@Level)
FETCH NEXT FROM TreeCur into @tID,@tName,@tParentID
End
Close TreeCur
Deallocate TreeCur
return
End

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-16 : 20:02:56

From SQL Server Books Online:

quote:

A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query. Individual queries may not support nesting up to 32 levels. A subquery can appear anywhere an expression can be used, if it returns a single value.

So 32 is indeed the max. 97 deep is not going to work, nor will it perform very well.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-16 : 21:34:53

I think this does what you are trying to
http://www.nigelrivett.net/RetrieveTreeHierarchy.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources