如果要用 T-SQL 來寫「遞迴」的話，一般來說 Stored Procedure 的上限是 32 次，而 CTE 卻跟 Stored Procedure 有著不同的上限次數，且預設為 100 次！而 CTE 遞迴的上限是可以修改的，最大值為 32,767 次！
參考資料：
Recursive Queries Using Common Table Expressions
http://msdn2.microsoft.com/EN-US/library/ms186243.aspx
Note:
An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. When testing the results of a recursive query, you can limit the number of recursion levels allowed for a specific statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. For more information, see Query Hint (Transact-SQL) and WITH common_table_expression (Transact-SQL).
Query Hint (Transact-SQL)
http://msdn2.microsoft.com/EN-US/library/ms181714.aspx
MAXRECURSION number
Specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.
When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.
Because of this error, all effects of the statement are rolled back. If the statement is a SELECT statement, partial results or no results may be returned. Any partial results returned may not include all rows on recursion levels beyond the specified maximum recursion level.