sql server 存储过程递归，寻找叶子

USE [DB1]
GO
/****** Object:  StoredProcedure [dbo].[sp_JDE_FetchBomDetail]    Script Date: 07/28/2011 09:47:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_JDE_FetchBomDetail](
    @ParentItem nvarchar(30),
    @ParentQty real,
    @OrignItem nvarchar(30), 
    @isLastLeaf int output) AS
BEGIN
   
    DECLARE @tmpVar int
    DECLARE @strParentItem nvarchar(30)
    DECLARE @p_item nvarchar(30)
    DECLARE @p_uom nvarchar(30)
    DECLARE @s_itm real
    DECLARE @s_litm nvarchar(30)
    DECLARE @s_atim nvarchar(30)
    DECLARE @s_uom nvarchar(10)
    DECLARE @s_qty real
    DECLARE @p_OUom nvarchar(10)
    DECLARE @S_Oum nvarchar(10)
    DECLARE @isLastLeaf2 int
   
    SET @tmpVar = 0;
    SET @isLastLeaf =0;
    SET @strParentItem = @ParentItem;
   
    DECLARE @rowcount AS int

    DECLARE mycur2 CURSOR Local FOR
   
        select ixkitl, cpuom, ixitm, ixlitm, ixaitm, csum, isnull(csqty,0), ixuom, ixum
        from costbom_Z1 where rtrim(ltrim(ixkitl)) =rtrim(ltrim(@strParentItem));
       
    OPEN mycur2;

    FETCH NEXT FROM mycur2
    INTO @p_item, @p_uom , @s_itm, @s_litm, @s_atim , @s_uom , @s_qty, @p_Ouom, @s_Oum;
   
--PRINT 'Count:  '+cast(@ROWCOUNT AS nvarchar(20))

    IF @@FETCH_STATUS <>0
    BEGIN
        SET @isLastLeaf=1
       
        CLOSE mycur2
        DEALLOCATE mycur2
       
        RETURN
    END
   
    ELSE BEGIN
--PRINT 'isLeastLeaf21:'+cast(isnull(@isLastLeaf2,'') AS nvarchar(20))   
        WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @qty real
            SET @qty=@s_qty*@ParentQty
           
            EXEC dbo.sp_JDE_FetchBomDetail @s_litm,@qty, @OrignItem, @isLastLeaf2 output;
           
--PRINT 'isLeastLeaf22:'+cast(@isLastLeaf2 AS nvarchar(20))   
       
            IF @isLastLeaf2=1
            BEGIN
                insert into  costbom_z3 (ixkitl, ixitm, ixlitm, ixaitm,csum, csqty, cpuom, ixum, ixuom )
                values(@OrignItem,  @s_itm, @s_litm, @s_atim , @s_uom , @s_qty*@ParentQty, @p_uom, @s_Oum, @P_Ouom);
            END
           
            FETCH NEXT FROM mycur2
            INTO @p_item, @p_uom , @s_itm, @s_litm, @s_atim , @s_uom , @s_qty, @p_Ouom, @s_Oum;
        END
    END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Factorial_ap](@Number Int,@RetVal Int OUTPUT)AS
begin
DECLARE @In Int
DECLARE @Out Int
IF @Number <> 1 BEGIN
    set @In = @Number-1
    EXEC Factorial_ap @In, @Out OUTPUT
    SELECT @RetVal = @Number * @Out
END
ELSE BEGIN
    SELECT @RetVal = 1
END
print @RetVal
RETURN
end
-- exec dbo.[Factorial_ap] 5,''
    CLOSE mycur2;
    DEALLOCATE mycur2; 


END