Рабочие зарисовки

[seajey]

Есть таблица со статьями учёта.
Нужно написать функцию, которая бы для входящей статьи выдавала бы полный список подстатей.
У статьи может быть несколько уровней вложенности, и нужно средствами T-SQL сделать рекурсивный обход.
В итоге всё получилось достаточно просто. Хотя лично я и помучился пару часов.


Сначала делаем вспомогательную функцию fSelectChildrenOfService(), возвращающую результат выполнения запроса следующего вида:

SELECT 
ServiceID, 
ServiceShort
FROM Services
WHERE ParentID = @inServiceID
	AND CDate IS NULL

Где @inServiceID — идентификатор статьи, а пучстой CDate, он же CancelationDate — признак того, что запись всё ещё актуальна.

Далее, в основной функции fSelectServicesHierarchy() создаём
переменную табличку, которую будем возвращать вовне:

RETURNS @ResultTable TABLE
(  
ServiceID dID,
ServiceShort dShortString,
isChecked INT
)

Делаем нулевой проход:

INSERT INTO @ResultTable
(
ServiceID,
ServiceShort,
isChecked
)
SELECT 
ServiceID, 
ServiceShort,
0
FROM dbo.fSelectChildrenOfService(@inServiceID) 
ORDER BY ServiceShort

Теперь у нас есть список подстатей для исходной входящей статьи.
Найдём подстатьи и для них — для этого нам уже понадобится запустить курсор:

DECLARE curServices CURSOR
LOCAL READ_ONLY  FAST_FORWARD  
FOR 
	SELECT ServiceID
	FROM @ResultTable

OPEN curServices
 
FETCH NEXT FROM curServices INTO @decServiceID
WHILE (@@fetch_status <> -1) 
BEGIN 
IF (@@fetch_status <> -2) 
	Begin 

	INSERT INTO @ResultTable
	(
	ServiceID, 
	ServiceShort,
	isChecked
	)
	SELECT 
	ServiceID, 
	ServiceShort,
	0
	FROM dbo.fSelectChildrenOfService(@decServiceID) AS f
	ORDER BY ServiceShort
		
	End
FETCH NEXT FROM curServices INTO @decServiceID
END  

CLOSE curServices
DEALLOCATE curServices

Нда, курсор в T-SQL — это, конечно, жутко громоздкая конструкция, и запомнить её целиком малореально. Ну да ладно.
Итак, мы дошли до второго уровня вложенности. А если их ещё больше? Не копипастить же втупую, причём неизвестное количество раз?

Очевидно, курсор нужно как-то завернуть в цикл, чтобы он проходил по результатам предыдущего шага.
В тело курсора (после основного запроса) вставляем дополнительный запрос на обновление:

UPDATE @ResultTable
SET isChecked = 1
WHERE ServiceID = @decServiceID

С его помощью ставим метку, что данную статью мы уже обработали. А сам запрос оборачиваем в цикл WHILE, со следующим условием:

WHILE EXISTS (SELECT ServiceID FROM @ResultTable WHERE isChecked = 0)

Т.е. пока в результирующей таблице есть необработанные статьи, продолжаем искать субстатьи.