It can go up to 5 levels deep, no more than that. Re: the UDF, that's a good idea, I'll take a look at the link, thanks!
> Sorry that I answer didn't take inheritance into account. Is there a > maximum number of levels of subcategories, or will it always be an > unknown? I have done looping with tSQL in the past using cursors, and > it really is a pain. > > You could use a UDF (User Defined Function) and return a table > variable. Here is a link for an example of returning a table > variable: http://sqlt.tripod.com/recursivity.htm > > You could probably modify this example to return a bit value if the > category or any parent has a product assigned. In theory if you write > the function correctly you could write a single update statement that > sets the hasproducts column equal to the funtion value passing in the > category as a parameter. > > Something like > UPDATE categories set hasprodutcs = fnHasProduct(category) > > where fnHasProduct id the name of your UDF and accepts the category as > a parameter. > > The real work would be in writing your UDF. > > You would want you to thoroughly test your UDF, but it could provide > you with a powerful and simple execution that even incorporates a > little OOP using tSQL. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341571 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
