Is recursion good in SQL Server?

With the new MS SQL 2005 you could use the WITHkeyword

Check out this question and particularly this answer.

With Oracle you could use CONNECT BY keyword to generate hierarchical queries (syntax).

AFAIK with MySQL you'll have to use the recursion.

Alternatively you could always build a cache table for your records parent->child relationships

As a general answer, it is possible to do some pretty sophisticated stuff in SQL Server that normally needs recursion, simply by using an iterative algorithm. I managed to do an XHTML parser in Transact SQL that worked surprisingly well. The the code prettifier I wrote was done in a stored procedure. It aint elegant, it is rather like watching buffalo doing Ballet. but it works .

Are you using SQL 2005?

If so you can use Common Table Expressions for this. Something along these lines:

;with CTE (Some, Columns, ItemId, ParentId) as (    select Some, Columns, ItemId, ParentId    from myTable     where ItemId = @itemID    union all    select a.Some, a.Columns, a.ItemId, a.ParentId    from myTable as a    inner join CTE as b on a.ParentId = b.ItemId    where a.ItemId <> b.ItemId)select * from CTE