递归存储过程中使用cursor

 

注意定义成local类型的cursor ！

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

kitesky