遞迴儲存過程中使用cursor

遞迴儲存過程中使用cursor可以用來向上遍歷有向圖，找出其直系父輩。

注意定義成local型別的cursor ！

[@more@]

PROCEDURE sp_get_all_lineal_parent
@node_id int,
@lineal_path varchar(200) output
with encryption
AS

SET NOCOUNT ON
BEGIN
IF ISNULL(@lineal_path, 'NULL') = 'NULL' SET @lineal_path = ''
IF EXISTS( select DependentProductID from Dependence_TB where ProductID = @node_id )
BEGIN
DECLARE cur_parent_node CURSOR FAST_FORWARD local FOR
SELECT DependentProductID
FROM Dependence_TB
WHERE ProductID = @node_id

DECLARE @parent_node_id int

OPEN cur_parent_node
FETCH NEXT FROM cur_parent_node INTO @parent_node_id
WHILE @@FETCH_STATUS = 0
BEGIN
set @lineal_path = @lineal_path + ',' + cast(@parent_node_id as varchar)
exec sp_get_all_lineal_parent @parent_node_id, @lineal_path output

FETCH NEXT FROM cur_parent_node INTO @parent_node_id
END
CLOSE cur_parent_node
DEALLOCATE cur_parent_node
END
insert into ##temp_a(a) select 100
select * from ##temp_a
END

--呼叫示例begin
declare @path varchar(200)
create table ##temp_a(a int)
EXEC sp_get_all_lineal_parent 12, @path output
drop table ##temp_a
select @path
end