rot299
asked on
Recursive query
I have a simple parent / child relationship setup in a table using the fields "Id" and "parentId" to create the relationship. If I pass an "Id", Is it possible to run a query that would recursively get all rows underneath it and the ones underneath them and so on til there are no more child elements?
Any examples would be great.
Thanks.
Any examples would be great.
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My example is recursive.
on the line
SET @count = @count + dbo.get_section_leaf_count
the function calls itself.
on the line
SET @count = @count + dbo.get_section_leaf_count
the function calls itself.
oops sorry bukko. didnt spot that.
ASKER
Thanks for all your help.
Im not advanced at SQL so KarinLoos' example is more straight forward and easier for me to understand so I think I will use that example. Is there any reason why the UDF would be a better choice?
Im not advanced at SQL so KarinLoos' example is more straight forward and easier for me to understand so I think I will use that example. Is there any reason why the UDF would be a better choice?
It depends on how you want to use it:
With a function you can refer to the function within a select statement
EG: SELECT * FROM MyFunction() - for rowset functions
SELECT dbo.MyFunction2() --for single value return functions
but you cant do that with stored procedures.
In other words if you waht to take the results and build upon them then it is better in a function If all you want to do is return the results to a client its better in stored proc.
of course you can have the dest of both worlds by having it in a function and then have a sotre proc that calls the function (but not the oposite way).
Confused ? Me 2 ;)
With a function you can refer to the function within a select statement
EG: SELECT * FROM MyFunction() - for rowset functions
SELECT dbo.MyFunction2() --for single value return functions
but you cant do that with stored procedures.
In other words if you waht to take the results and build upon them then it is better in a function If all you want to do is return the results to a client its better in stored proc.
of course you can have the dest of both worlds by having it in a function and then have a sotre proc that calls the function (but not the oposite way).
Confused ? Me 2 ;)
ASKER
Ill stick with the sp for the moment as it does what I need. I might look into the UDF at a later stage.
Thanks again.
Thanks again.
Just a little note: You will notice that these solutions are itterative rather than recursive. SQL Server is pretty restrictive when it comes to recursion, yes it can be done but only to 16 levels.