存储过程调用存储过程，报错：超出了存储过程、函数、触发器或视图的最大嵌套层数,急

Question

sql最大层数为 32

Answer 1

接上：3：ALTER PROC [dbo].[happ.dcr_cal_work_middle_material]
    @TRADE_CODE VARCHAR(50) ,
    @COP_EMS_NO VARCHAR(50) ,
    @DCR_TIMES INT ,
    @cop_parent_no VARCHAR(64) ,
    @parent_work_order VARCHAR(64) ,
    @order_no VARCHAR(64) ,
    @cop_img_g_no NUMERIC(9, 0) ,
    @cop_img_qty NUMERIC(18, 9)
AS 
    BEGIN
        DECLARE @work_order VARCHAR(50) ;
        DECLARE @work_qty NUMERIC(18, 9) ;
        DECLARE cursor3 CURSOR local
        FOR
            SELECT  C.WORK_NO ,
                    @cop_img_qty * C.IQT / B.QT
            FROM    ( SELECT    A.INVOICE_NO ,
                                SUM(A.IN_OUT_QTY) QT
                      FROM      ( SELECT DISTINCT
                                            T.INVOICE_NO ,
                                            t1.WORK_NO ,
                                            T.IN_OUT_QTY
                                  FROM      COP_OUT_STOCK_LOT_WXGF t
                                            LEFT JOIN MATERIAL_W T2 ON T.COP_G_NO = T2.COP_G_NO
                                            LEFT JOIN COP_IN_STOCK_LIST_WXGF t1 ON t.COP_G_NO = t1.COP_G_NO
                                                              AND t.MAKE_LOT_NO = t1.IN_NO
                                  WHERE     t.INVOICE_NO = @order_no
                                            AND t.INVOICE_G_NO = @cop_img_g_no
                                            AND T.IN_OUT_QTY > 0
                                            AND T2.I_license <> 'S'
                                            AND T2.I_license IS NOT NULL
                                  UNION
                                  SELECT DISTINCT
                                            T.INVOICE_NO ,
                                            t1.R_D_NO AS WORK_NO ,
                                            T.IN_OUT_QTY
                                  FROM      COP_OUT_STOCK_LOT_WXGF t
                                            LEFT JOIN MATERIAL_W T2 ON T.COP_G_NO = T2.COP_G_NO
                                            LEFT JOIN COP_RETOURNER_WXGF t1 ON t.COP_G_NO = t1.P_NAME
                                                              AND t.MAKE_LOT_NO = t1.OUT_NO
                                  WHERE     t.INVOICE_NO = @order_no
                                            AND t.INVOICE_G_NO = @cop_img_g_no
                                            AND T.IN_OUT_QTY > 0
                                            AND T2.I_license = 'S'
                                            AND T2.I_license IS NOT NULL
                                ) A
                      GROUP BY  A.INVOICE_NO
                    ) B
                    LEFT JOIN ( SELECT  D.INVOICE_NO ,
                                        D.WORK_NO ,
                                        SUM(D.IN_OUT_QTY) IQT
                                FROM    ( SELECT DISTINCT
                                                    T.INVOICE_NO ,
                                                    t1.WORK_NO ,
                                                    T.IN_OUT_QTY
                                          FROM      COP_OUT_STOCK_LOT_WXGF t
                                                    LEFT JOIN MATERIAL_W T2 ON T.COP_G_NO = T2.COP_G_NO
                                                    LEFT JOIN COP_IN_STOCK_LIST_WXGF t1 ON t.COP_G_NO = t1.COP_G_NO
                                                              AND t.MAKE_LOT_NO = t1.IN_NO
                                          WHERE     t.INVOICE_NO = @order_no
                                                    AND t.INVOICE_G_NO = @cop_img_g_no
                                                    AND T.IN_OUT_QTY > 0
                                                    AND T2.I_license <> 'S'
                                                    AND T2.I_license IS NOT NULL
                                          UNION
                                          SELECT DISTINCT
                                                    T.INVOICE_NO ,
                                                    t1.R_D_NO AS WORK_NO ,
                                                    T.IN_OUT_QTY
                                          FROM      COP_OUT_STOCK_LOT_WXGF t
                                                    LEFT JOIN MATERIAL_W T2 ON T.COP_G_NO = T2.COP_G_NO
                                                    LEFT JOIN COP_RETOURNER_WXGF t1 ON t.COP_G_NO = t1.P_NAME
                                                              AND t.MAKE_LOT_NO = t1.OUT_NO
                                          WHERE     t.INVOICE_NO = @order_no
                                                    AND t.INVOICE_G_NO = @cop_img_g_no
                                                    AND T.IN_OUT_QTY > 0
                                                    AND T2.I_license = 'S'
                                                    AND T2.I_license IS NOT NULL
                                        ) D
                                GROUP BY D.INVOICE_NO ,
                                        D.WORK_NO
                              ) C ON B.INVOICE_NO = C.INVOICE_NO
        OPEN cursor3
        FETCH NEXT FROM cursor3 INTO @work_order, @work_qty ;
        WHILE @@fetch_status = 0 
            BEGIN
                EXEC [happ.dcr_cal_work_order] @TRADE_CODE, @COP_EMS_NO,
                    @DCR_TIMES, @cop_parent_no, @parent_work_order,
                    @work_order, @work_qty ;
    
                FETCH NEXT FROM cursor3 INTO @work_order, @work_qty ;
            END
        CLOSE cursor3                        DEALLOCATE cursor3
       END

Answer 2

这个是因为你的存储过程中，又调用了存储过程。比如你有一个a存储过程，但是在你的代码中又调用了a，比如：create proc dbo.a
asexec dbo.a
go这样就是递归调用了，当超过了32层，就会报错。所以必须要限制调用次数，否则就会陷入到死循环了。

Answer 3

简化版1：ALTER PROC [dbo].[happ.dcr_work_order_to_bom]
AS 
    BEGIN
        DECLARE @cop_exg_no VARCHAR(64) ;
    
        DECLARE cursor1 CURSOR
        FOR
            SELECT  EXG_NO ,
            FROM    COP_WORK_HEAD
            
        OPEN cursor1
        FETCH NEXT FROM cursor1 INTO @cop_exg_no
        WHILE @@fetch_status = 0 
            BEGIN
                EXEC [happ.dcr_cal_work_order] @cop_exg_no,
                        
                FETCH NEXT FROM cursor1 INTO @cop_exg_no
            END
        CLOSE cursor1                          DEALLOCATE cursor1
    
    END2： ALTER PROC [dbo].[happ.dcr_cal_work_order]
    @cop_parent_no VARCHAR(64)
AS 
    BEGIN
        DECLARE @order_no VARCHAR(50) ;
        
        DECLARE cursor2 CURSOR local
        FOR
            SELECT  T.IN_OUT_NO
            FROM    COP_OUT_STOCK_LIST_WXGF T
            WHERE   T.WORK_NO = @cop_parent_no
        
        OPEN cursor2
        FETCH NEXT FROM cursor2 INTO @order_no
        WHILE @@fetch_status = 0 
            BEGIN
                EXEC [happ.dcr_cal_work_middle_material] @order_no
     
                FETCH NEXT FROM cursor2 INTO @order_no
            END
        CLOSE cursor2                         DEALLOCATE cursor2
     
    END3： ALTER PROC [dbo].[happ.dcr_cal_work_middle_material] @order_no VARCHAR(64)
AS 
    BEGIN
        DECLARE @work_order VARCHAR(50) ;
        DECLARE cursor3 CURSOR local
        FOR
            SELECT  WORK_NO
            FROM    COP_OUT_STOCK_LOT_WXGF
            WHERE   INVOICE_NO = @order_no
        OPEN cursor3
        FETCH NEXT FROM cursor3 INTO @work_order ;
        WHILE @@fetch_status = 0 
            BEGIN
                EXEC [happ.dcr_cal_work_order] @work_order
    
                FETCH NEXT FROM cursor3 INTO @work_order ;
            END
        CLOSE cursor3                        DEALLOCATE cursor3
  
    END

Answer 4

这种嵌套很容易出错，如果每个sp的逻辑不是非常复杂的话就集成在一个sp或者2个sp里面