Adam Howitt wrote: > You may want to check out Joe Celco's nested set model (no refs but google > should find it) for a neat way to handle this type of hierarchical > information storage. In the meantime you could keep the cursor but insert > the resulting recordsets into a working table before returning the complete > resultset. You would create the working table if @depth = 1 (set a local > variable and pass it to subsequent calls and drop it after the last call) or > permanently create it which would give you better performance and the > ability to use indexes.
I highly recommend his book, SQL for Smarties[1]. Here's a few links that should help: 1. http://www.intelligententerprise.com/001020/celko.jhtml 2. http://www.dbmsmag.com/9604d06.html and http://www.dbmsmag.com/9603d06.html 3. http://troels.arvin.dk/db/rdbms/links/ 4. http://www.onlamp.com/pub/a/onlamp/2004/08/05/hierarchical_sql.html 5. http://dbazine.com/tropashko4.shtml 6. http://www.sitepoint.com/article/hierarchical-data-database He also wrote a whole book on trees and hierarchies[2]. K. [1] http://www.amazon.co.uk/exec/obidos/ASIN/1558605762/ [2] http://www.amazon.co.uk/exec/obidos/ASIN/1558609202/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201392 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
