Be careful with recursion in MS SQL server 2000. There is a limit of 32 proc calls deep. Not sure about functions, though. This would have obvious limitations of your structure was more than 32 levels deep.
I can cook up a coded sample of the iterative method if you want. I'm not sure what you wish to do with this level count, but be careful if you plan on storing it in the original table. The level count is already stored inherently in the table through its structure. Populating a column with that number would be de-normalizing that piece of data (for understandable performance reasons). Now if any of the cat_parent_id values are allowed to change, you will effectively be "cutting and pasting" a branch of your tree to a different location with different ancestor nodes. This will change your level counts for the changed node and all it's descendants. Still doable, just overhead to consider. ~Brad I will see what i can find on recursion that might help me out. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230828 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
