ok guys - here we go - i modified yours a small bit mark and i got it to return subareas of subareas etc

CREATE  FUNCTION dbo.GetReports3(@IncludeParent bit, @intParentPlantAreaID int)

RETURNS @retFindReports...

Thanks again for all your help!

Each parent can have children but these children nodes can also have children themselves etc

So what I need to do is use the plantareaid of...

Thanks so much for all the replies! I got the recursion working for my originla but can seem to get it to recall for areas that are down deep in...