递归存储过程中使用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