UNION ALL

-- Recursive Member Query that references myRecursiveCTE

)

При написании пользовательской рекурсивной процедуры, не содержащей выражений CTE, необходимо включить явное завершающее предложение. Завершающее предложение отвечает за обеспечение завершения (в конце концов) рекурсивного алгоритма и возвращение наверх по стеку рекурсивных вызовов. Без этого предложения ваш код может уйти в бесконечный цикл.

Выражения CTE обладают двумя свойствами, которые облегчают работу с завершающими предложениями. Первое состоит в том, что если рекурсивный член возвращает пустые записи, возникает неявное завершающее предложение. В таком случае запрос рекурсивного члена не обращается рекурсивно к выражению CTE, а вместо этого возвращает наверх стек вызовов. Другая особенность состоит в том, что имеется возможность явно установить уровень MAXRECURSION.

Уровень MAXRECURSION можно установить явно в пакете, содержащем CTE, или посредством параметра на серверной стороне (по умолчанию в рамках сервера действует значение 100, если только оно не было изменено). Этот параметр ограничивает число вызовов выражения CTE, обращенных к нему самому. При достижении предельного значения создается исключение. В синтаксисе для установки уровня MAXRECURSION необходимо использовать предложение OPTION в операторе SELECT, следующим за выражением CTE, как показано далее

SELECT * FROM CTE

OPTION (MAXRECURSION 7);

Существуют некоторые другие правила, о которых следует помнить при построении рекурсивных выражений CTE. В рекурсивное выражение CTE должен входить как закрепленный, так и рекурсивный элемент. Оба элемента должны иметь одинаковое число столбцов, и столбцы, принадлежащие обоим элементам, должны иметь одинаковые типы данных. Рекурсивный элемент может ссылаться на выражение CTE только один раз, и в элементах не разрешается использовать следующие предложения или ключевые слова.

Ø SELECT DISTINCT

Ø GROUP BY

Ø HAVING

Ø TOP

Ø LEFT/RIGHT OUTER JOIN

Пример простой иерархии [Frederic BROUARD (оригинал: Recursive Queries in SQL:1999 and SQL Server 2005) Перевод Моисеенко С.И. ]

Создадим таблицу, которая содержит типологию транспортных средств: