22,209
社区成员
发帖
与我相关
我的任务
分享存储过程调用存储过程，报错：超出了存储过程、函数、触发器或视图的最大嵌套层数,急
在计算一个未知层数的BOM展开，我用了存储过程嵌套，计算的时候报错：超出了存储过程、函数、触发器或视图的最大嵌套层数(最大层数为 32)。
求解决或优化办法，小的头一次这么写就遇到这问题，感谢各位帮忙。
附上3个涉及到的存储过程，依次执行过来，
1：
2：
求解决或优化办法，小的头一次这么写就遇到这问题，感谢各位帮忙。
附上3个涉及到的存储过程，依次执行过来，
1：
ALTER PROC [dbo].[happ.dcr_work_order_to_bom]
@TRADE_CODE VARCHAR(50) ,
@COP_EMS_NO VARCHAR(50) ,
@DCR_TIMES INT
AS
BEGIN
DECLARE @cop_exg_no VARCHAR(64) ;
DECLARE @work_order VARCHAR(50) ;
DECLARE @work_qty NUMERIC(18, 9) ;
DELETE FROM COP_BOM
WHERE TRADE_CODE = @TRADE_CODE
AND COP_EMS_NO = @COP_EMS_NO
AND DCR_TIMES = @DCR_TIMES
DECLARE cursor1 CURSOR
FOR
SELECT EXG_NO ,
WORK_NO ,
IN_QTY
FROM COP_WORK_HEAD
WHERE TRADE_CODE = @TRADE_CODE
AND COP_EMS_NO = @COP_EMS_NO
AND DCR_TIMES = @DCR_TIMES ;
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @cop_exg_no, @work_order, @work_qty
WHILE @@fetch_status = 0
BEGIN
EXEC [happ.dcr_cal_work_order] @TRADE_CODE, @COP_EMS_NO,
@DCR_TIMES, @cop_exg_no, @cop_exg_no, @work_order,
@work_order, @work_qty;
FETCH NEXT FROM cursor1 INTO @cop_exg_no, @work_order,
@work_qty ;
END
CLOSE cursor1
DEALLOCATE cursor1
END2：
ALTER PROC [dbo].[happ.dcr_cal_work_order]
@TRADE_CODE VARCHAR(50) ,
@COP_EMS_NO VARCHAR(50) ,
@DCR_TIMES INT ,
@cop_parent_no VARCHAR(64) ,
@parent_work_order VARCHAR(64) ,
@work_order VARCHAR(50) ,
@work_qty NUMERIC(18, 9)
AS
BEGIN
DECLARE @order_no VARCHAR(50) ;
DECLARE @cop_img_no VARCHAR(64) ;
DECLARE @cop_img_qty NUMERIC(18, 9) ;
DECLARE @cop_img_g_no NUMERIC(9, 0) ;
INSERT INTO COP_BOM
( COP_EXG_NO ,
COP_IMG_NO ,
BOM_VERSION ,
DEC_CM ,
DEC_DM ,
COP_IMG_NO_T ,
TRADE_CODE ,
COP_EMS_NO ,
DCR_TIMES
)
SELECT DISTINCT
@cop_parent_no ,
T.IO_COP_G_NO ,
@parent_work_order ,
@work_qty * T1.YCGQ ,
0 ,
T.COP_G_NO_R ,
@TRADE_CODE ,
@COP_EMS_NO ,
@DCR_TIMES
FROM COP_OUT_STOCK_LIST_WXGF T
LEFT JOIN ( SELECT W_ORDER_NO ,
M_NO ,
G_NO_T ,
( SUM(Y_QTY) + SUM(C_QTY) )
/ MAX(G_QTY) YCGQ
FROM COP_LISTE_WXGF
WHERE G_QTY > 0
GROUP BY W_ORDER_NO ,
M_NO ,
G_NO_T
) T1 ON T.WORK_NO = T1.W_ORDER_NO
AND T.IO_COP_G_NO = T1.M_NO
AND T.COP_G_NO_R = T1.G_NO_T
WHERE T.WORK_NO = @work_order
AND T.D_NO = 99
DECLARE cursor2 CURSOR local
FOR
SELECT T.IN_OUT_NO ,
T.IO_COP_G_NO ,
@work_qty * T1.YCGQ * ( T.IO_QTY / T2.TQ ) ,
T.LIST_G_NO
FROM COP_OUT_STOCK_LIST_WXGF T
LEFT JOIN ( SELECT W_ORDER_NO ,
M_NO ,
( SUM(Y_QTY) + SUM(C_QTY) )
/ MAX(G_QTY) YCGQ
FROM COP_LISTE_WXGF
WHERE G_QTY > 0
GROUP BY W_ORDER_NO ,
M_NO
) T1 ON T.WORK_NO = T1.W_ORDER_NO
AND T.IO_COP_G_NO = T1.M_NO
LEFT JOIN ( SELECT T.IO_COP_G_NO ,
SUM(T.IO_QTY) TQ
FROM COP_OUT_STOCK_LIST_WXGF T
WHERE T.WORK_NO = @work_order
AND T.D_NO != 99
AND IO_QTY > 0
GROUP BY T.IO_COP_G_NO
) T2 ON T.IO_COP_G_NO = T2.IO_COP_G_NO
WHERE T.WORK_NO = @work_order
AND T.D_NO != 99 ;
OPEN cursor2
FETCH NEXT FROM cursor2 INTO @order_no, @cop_img_no, @cop_img_qty,
@cop_img_g_no
WHILE @@fetch_status = 0
BEGIN
EXEC [happ.dcr_cal_work_middle_material] @TRADE_CODE,
@COP_EMS_NO, @DCR_TIMES, @cop_parent_no,
@parent_work_order, @order_no, @cop_img_g_no, @cop_img_no,
@cop_img_qty ;
FETCH NEXT FROM cursor2 INTO @order_no, @cop_img_no,
@cop_img_qty, @cop_img_g_no
END
CLOSE cursor2
DEALLOCATE cursor2
END
...全文
请发表友善的回复…
发表回复
發糞塗牆 2014-03-18
- 打赏
- 举报
这种嵌套很容易出错，如果每个sp的逻辑不是非常复杂的话就集成在一个sp或者2个sp里面
wuaiding 2014-03-18
- 打赏
- 举报
请问我需要怎么修改为循环？
我现在是 有2存储过程得到结果，然后进入3存储过程再次计算，得到结果再返回2计算，
如此计算下去，直到查不到数据为止。
麻烦能否依照简化版本给个实例？
LongRui888 2014-03-18
- 打赏
- 举报
现在问题是调用次数我也不能确定，预计是小于等于40次，大于32层的限制了啊，有优化方法吗？[/quote]
这个好像没办法设置，让存储过程的递归能超过32层呢。
你试试把这个递归的存储过程，修改为循环的把
發糞塗牆 2014-03-18
- 打赏
- 举报
用while循环试试
wuaiding 2014-03-18
- 打赏
- 举报
现在问题是调用次数我也不能确定，预计是小于等于40次，大于32层的限制了啊，有优化方法吗？
wuaiding 2014-03-18
- 打赏
- 举报
简化版
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
ENDALTER 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
ENDALTER 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
ENDLongRui888 2014-03-18
- 打赏
- 举报
这个是因为你的存储过程中，又调用了存储过程。
比如你有一个a存储过程，但是在你的代码中又调用了a，比如：
create proc dbo.a
as
exec dbo.a
go
这样就是递归调用了，当超过了32层，就会报错。
所以必须要限制调用次数，否则就会陷入到死循环了。
--小F-- 2014-03-18
- 打赏
- 举报
TO LONG..只要核心代码。
wuaiding 2014-03-18
- 打赏
- 举报
接上：
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
