Recursive SQL can be very elegant and efficient. However, because of the difficulty developers can have understanding recursion, it is sometimes thought of as “too inefficient to use frequently.” But, ...

The basic concept of recursion is straightforward: a given chunk of code calls itself until some boundary condition is reached. Arthur Fuller demonstrates how to use recursion in T-SQL.

Processing occurs one at a time and calls a SQL Server stored procedure. The SPROC is recursive. For each ID that is submitted, it can have any number of child ID's.