Хранимая процедура, обновляющая уровни иерархии в любой иерархической таблице
CREATE PROCEDURE UpdateHierarchyLevels
 @TableName SfaString,
 @IdColumnName SfaString
AS
———————————————————
— Описание:
— Обновление значений уровня иерархии в стандартной иерархической таблице.
— Параметры:
— @TableName - имя таблицы, в которой проводятся обновления. 
— @IdColumnName - имя столбца с идентификатором строки, на который ссылается ParentId
———————————————————
SET nocount ON

DECLARE @SqlStr AS varchar(8000)

— Сначала очистка уровней иерархии, чтобы были изменения 
— на каждом шаге итерации.
SELECT @SqlStr =
 ?UPDATE ? + @TableName +
 ? SET HierarchyLevel = 0 ?
EXEC (@SqlStr)

— Установка уровня иерархии самого верхнего элемента.
— У самого верхнего элемента родитель не определен, IS NULL.
SELECT @SqlStr =
 ?UPDATE ?+ @TableName +
 ? SET HierarchyLevel = 1 ? +
 ? WHERE ParentId IS NULL?
EXEC (@SqlStr)

— Итеративное задание уровней иерархии для дочерних элементов. 
— Дочерним является такой элемент, у которого родитель входит в 
— множество записей текущего уровня иерархии. 
— Задание для них уровня иерархии равным текущему уровню + 1.
— Провести итерацию, если были сделаны изменения. 

DECLARE @CurrentHierarchyLevel _
 AS smallint
DECLARE @NumOfRecordsUpdated AS int

SELECT @CurrentHierarchyLevel = 1
SELECT @NumOfRecordsUpdated = 1
— Проверка начала итерации 

WHILE ((@CurrentHierarchyLevel < 255)
— Проверка отсутствия бесконечного цикла 
 AND (@NumOfRecordsUpdated > 0))
— Прекращение итераций при отсутствии изменений 
 BEGIN
 SELECT @SqlStr =
 ?UPDATE TChild ? +
 ? SET HierarchyLevel = ? + _
 CONVERT(varchar(10), _
 @CurrentHierarchyLevel _
 + 1) +
 ? FROM ? + @TableName + ? TChild, ?_
 @TableName + _
 ?TParent? +
 ? WHERE TChild.ParentId = TParent.? _
 + @IdColumnName +
 ? AND TParent.HierarchyLevel = ?_
 + CONVERT(varchar(10), _
 @CurrentHierarchyLevel)
 EXEC (@SqlStr)

 SELECT @NumOfRecordsUpdated = _
 @@RowCount
— Получение количества измененных записей 
 SELECT @CurrentHierarchyLevel = _
 @CurrentHierarchyLevel + 1
 END — WHILE

SET nocount OFF

Поделитесь материалом с коллегами и друзьями