Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Рекурсивная хранимая процедурка  [new]
harisma
Member

Откуда:
Сообщений: 243
Помогите написать несложную хранимую процедурку, которая принимала бы несколько строковых параметров и возвращала результат в виде строки.

Исходные данные:
- таблица с данными, у которой есть такие поля : NAME, ParentID, RecID + еще множество полей, какие нас не интересуют.

Пример данных в таблице

RECID ParentID Name
1 0 aaaa
2 0 bbbb
3 2 cccc
4 1 dddd
5 3 eeee
6 5 ffff
......

Задание:
- построить по начальному RECID на основе данных исходной таблицы цепочку значений из поля NAME вплоть до самого корневого значения.

Например, Дано исходное значение RECID = 6. Тогда в результате я должен получить следующую последовательность:
ffff|eeee|cccc|bbbb

Заранее спасибо.
27 апр 12, 17:56    [12481775]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Рекурсивное CTE без всяких рекурсивных процедур.
27 апр 12, 17:57    [12481780]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
harisma
Member

Откуда:
Сообщений: 243
Хорошо, можно и без процедур, но главный вопрос КАК это сделать?
27 апр 12, 17:59    [12481785]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
iljy
Member

Откуда:
Сообщений: 8711
harisma,

ищите в BOL примеры на рекурсивные CTE, там есть обход дерева
27 апр 12, 18:15    [12481884]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
harisma
Member

Откуда:
Сообщений: 243
Получилась у меня такая CTE:
with C (RecID, [Name], ParentID, HasChild, Level) AS
(
  Select B.RecID, B.[Name], B.ParentID, B.HasChild, 1 FROM Dictionaries_C3AD665D2FD8E140 as B 
    where PARENTID = '0000000000000000'
  UNION ALL
  select B.RecID, B.[Name], B.ParentID, B.HASCHILD, (Level + 1) from Dictionaries_C3AD665D2FD8E140 as B
  INNER Join C ON C.RecID = B.PARENTID
)

select * from C


Но она возвращает мне ВСЕ содержимое исходной таблицы, сгруппированое по уровням. А мне надо для какой-то одной конкретной записи, определенной значением поля RECID, получить цепочку значений. Как надо переделать этот скрипт, чтобы в результирующем наборе были только записи, от которых зависит начальная запись?
27 апр 12, 18:35    [12481974]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
Strangers
Member [заблокирован]

Откуда: Україна
Сообщений: 2613
Может так?
IF OBJECT_ID('TempDB..#k') IS NOT NULL DROP TABLE #k
CREATE TABLE #k (RECID int, ParentID int, Name varchar(200))

INSERT INTO #k
SELECT 1, 0, 'aaaa'
UNION SELECT 2, 0, 'bbbb'
UNION SELECT 3, 2, 'cccc'
UNION SELECT 4, 1, 'dddd'
UNION SELECT 5, 3, 'eeee'
UNION SELECT 6, 5, 'ffff'

DECLARE @cols varchar(max)
set @cols = ''

;WITH asd (ID, RECID, ParentID, Name) AS
(
SELECT RECID as ID, RECID, ParentID, Name FROM #k
UNION ALL
 SELECT z.ID, k.RECID, k.ParentID, k.Name FROM #k k
	JOIN asd z ON z.ParentID = k.RECID
)

SELECT @cols = case when @cols = '' then '' else  @cols+'/' end + cast(Name as varchar(200))
FROM asd
WHERE ID = 6

SELECT @cols
27 апр 12, 18:42    [12481991]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
harisma
Member

Откуда:
Сообщений: 243
RecIDNameParentIDHasChildLevel
10DE63419C1FB040тест1 тест1тест1 тест1тест1тест10000000000000000-11
28994C06571FB040dbgdfdfbdfdf dfg dgddghdhgh fgtghtrtrtrhtry trtrytr0000000000000000-11
3099AA18691FB040svfsddfdfvdfg gdgfghrhtrtryryytry ghtryrryutyyututyy ytyutyutyutyuty y0000000000000000-11
40F617CF45FBA940Тестовый товар 111000000000000000001
D0907CCAB01EB040тест1 тест1тест1 тест1тест1тест1 test1test1 teste10000000000000000-11
D0A5B75B1B00AA40sdfsdwe000000000000000001
E076597B4C2DAD40aaaaaam000000000000000001
E0F857E28D1FB040ghtrtrytrytutyuiy tyutyttyutyutytyhyrtuty ytyutyutyut ryutyuty0000000000000000-11
F0B6E4589DDDA940fgdfdfd000000000000000001
986D6C12568CB14012345678928994C06571FB04002
D046F5C48E44B040dffgdfgdghdt28994C06571FB04002
E8BD0547247EB140ytutiyui7iyu28994C06571FB04002


Это я получил

А вот что мне хотелось бы получить, если стартовым RECID был указан 986D6C12568CB140:
RecIDNameParentIDHasChildLevel
28994C06571FB040dbgdfdfbdfdf dfg dgddghdhgh fgtghtrtrtrhtry trtrytr0000000000000000-11
986D6C12568CB14012345678928994C06571FB04002
27 апр 12, 18:42    [12481995]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
harisma
Member

Откуда:
Сообщений: 243
Strangers,

Супер. То что надо :)
27 апр 12, 18:51    [12482021]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
harisma
Strangers,

Супер. То что надо :)
Ничего супер там нет. Ибо а) недокументировано и б) не определен порядок. Этот способ годится только для версий сервера 2000 и ниже. Воспользуйтесь документированным for xml path(''), type.
27 апр 12, 19:19    [12482102]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
harisma
Member

Откуда:
Сообщений: 243
invm
б) не определен порядок.

Ну, насчет порядка, то это легко решаемо. Например так:
IF OBJECT_ID('TempDB..#k') IS NOT NULL DROP TABLE #k
CREATE TABLE #k (RECID int, ParentID int, Name varchar(200))

INSERT INTO #k
SELECT 1, 0, 'aaaa'
UNION SELECT 2, 0, 'bbbb'
UNION SELECT 3, 2, 'cccc'
UNION SELECT 4, 1, 'dddd'
UNION SELECT 5, 3, 'eeee'
UNION SELECT 6, 5, 'ffff'

DECLARE @cols varchar(max)
set @cols = ''

;WITH asd (ID, RECID, ParentID, Name, Level) AS
(
SELECT RECID as ID, RECID, ParentID, Name, 1 FROM #k
UNION ALL
 SELECT z.ID, k.RECID, k.ParentID, k.Name, z.Level + 1 FROM #k k
	JOIN asd z ON z.ParentID = k.RECID
)


SELECT @cols = case when @cols = '' then '' else  @cols+'/' end + cast(Name as varchar(200))
FROM asd
WHERE ID = 6
order By Level

SELECT @cols


а то что недокументировано.... Так таких вещей есть много в скуле, которые недокументированы, но широко используются, бо НАДО :)
11 май 12, 13:34    [12536002]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
Glory
Member

Откуда:
Сообщений: 104751
harisma
а то что недокументировано.... Так таких вещей есть много в скуле, которые недокументированы, но широко используются, бо НАДО

Только в один прекрасный день перестает работать, как надо
11 май 12, 13:35    [12536025]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
neoddd
Member

Откуда:
Сообщений: 113
Glory
harisma
а то что недокументировано.... Так таких вещей есть много в скуле, которые недокументированы, но широко используются, бо НАДО

Только в один прекрасный день перестает работать, как надо


Вроде сам MS пользует и рекомендует:

http://blogs.msdn.com/b/sqlcat/archive/2011/04/28/optimize-recursive-cte-query.aspx
11 май 12, 13:57    [12536242]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
Glory
Member

Откуда:
Сообщений: 104751
neoddd
Вроде сам MS пользует и рекомендует:

http://blogs.msdn.com/b/sqlcat/archive/2011/04/28/optimize-recursive-cte-query.aspx

И где там order by ?
11 май 12, 13:58    [12536261]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
neoddd
Member

Откуда:
Сообщений: 113
Glory
И где там order by ?


Окей, order by не документировано. Ну так в случае ТС можно сначала слить в @t и там отсортировать, если уж заморачиваться.
Но я не претендую на советы, просто не понял сразу, в чем проблема с CTE.
11 май 12, 14:04    [12536328]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> Но я не претендую на советы, просто не понял сразу, в чем проблема с CTE.

с cte как раз все в порядке.
проблема вот в этом:
SELECT @cols = case when @cols = '' then '' else  @cols+'/' end + cast(Name as varchar(200))


вот такие, например, фокусы возможны:
http://support.microsoft.com/kb/287515/en-us

Posted via ActualForum NNTP Server 1.5

11 май 12, 14:07    [12536359]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
Glory
Member

Откуда:
Сообщений: 104751
neoddd
Окей, order by не документировано. Ну так в случае ТС можно сначала слить в @t и там отсортировать, если уж заморачиваться.
Но я не претендую на советы, просто не понял сразу, в чем проблема с CTE.

И как же он отсортирует внутри текстового поля подстроки ?
11 май 12, 14:07    [12536360]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивная хранимая процедурка  [new]
neoddd
Member

Откуда:
Сообщений: 113
daw
с cte как раз все в порядке.


Ну слава аллаху, я вообще в дискуссию влез только потому, что у нас рекурсия через CTE делается в некоторых местах, но все хорошо, что не испорчено :)
11 май 12, 14:14    [12536433]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить