Question

sql存储过程 貌似进入死循环了，不知道为什么

DECLARE tocdCur SCROLL CURSOR FOR
SELECT BussinessPartnerMaster.BP_ID,SalesOrder.[SECTION],
BussinessPartnerMaster.BILL_BP_ID,BussinessPartnerMaster.PAY_MONTH,
BussinessPartnerMaster.PAY_DAY
FROM BussinessPartnerMaster
LEFT OUTER JOIN SalesOrder
ON BussinessPartnerMaster.BP_ID=SalesOrder.CUSTOMER
WHERE BussinessPartnerMaster.BILL_TYPE=@BILL_TYPE
AND BussinessPartnerMaster.CLOSING_DAY=@CLOSING_DAY
/*AND BussinessPartnerMaster.BP_ID=(CASE WHEN @TOKCD ='' THEN BussinessPartnerMaster.BP_ID
ELSE @TOKCD END)*/
and BussinessPartnerMaster.BP_ID='167301'
GROUP BY BussinessPartnerMaster.BP_ID,SalesOrder.[SECTION],
BussinessPartnerMaster.BILL_BP_ID,BussinessPartnerMaster.PAY_MONTH,
BussinessPartnerMaster.PAY_DAY
OPEN tocdCur
FETCH tocdCur INTO @BP_ID, @SECTION, @BILL_BP_ID, @PAY_MONTH, @PAY_DAY
WHILE (@@FETCH_STATUS=0)
BEGIN
INSERT INVOICE_DETAIL(
[SECTION],CUSTOMER,BILL_BP_ID,
CLOSING_DAY,PRE_CLOSING_DAY,
PAY_DAY,PRE_AMOUNT_BILL,
RECEIPT_AMOUNT,SALES_AMOUNT,
DISCOUNT_RETURN,TAX_AMOUT,
AMOUNT_BILL
) VALUES
(@SECTION,@BP_ID,@BILL_BP_ID,
@CLOSING_DATE,@maxdate,
@plndate,@AMOUNT_BILL,
@receam,@pSALES_AMOUNT,
@mSALES_AMOUNT,@taxam,
@amount)
END
CLOSE tocdCur
DEALLOCATE tocdCur
额，解决掉了，循环内没写FETCH tocdCur INTO @BP_ID, @SECTION, @BILL_BP_ID, @PAY_MONTH, @PAY_DAY

• 你的回答被采纳后将获得：
• 系统奖励15（财富值+成长值）+难题奖励10（财富值+成长值）+提问者悬赏50（财富值+成长值）

Answer 1

完全可以不用这样写，效率太低下了

追问

额，那怎么写

追答

INSERT into INVOICE_DETAIL(
          [SECTION],CUSTOMER,BILL_BP_ID,
          CLOSING_DAY,PRE_CLOSING_DAY,
          PAY_DAY,PRE_AMOUNT_BILL,
          RECEIPT_AMOUNT,SALES_AMOUNT,
          DISCOUNT_RETURN,TAX_AMOUT,
          AMOUNT_BILL)
SELECT BussinessPartnerMaster.BP_ID,SalesOrder.[SECTION],
    BussinessPartnerMaster.BILL_BP_ID,BussinessPartnerMaster.PAY_MONTH,
    BussinessPartnerMaster.PAY_DAY
    FROM BussinessPartnerMaster
    LEFT OUTER JOIN SalesOrder
    ON BussinessPartnerMaster.BP_ID=SalesOrder.CUSTOMER
    WHERE BussinessPartnerMaster.BILL_TYPE=@BILL_TYPE
    AND BussinessPartnerMaster.CLOSING_DAY=@CLOSING_DAY
    and BussinessPartnerMaster.BP_ID='167301'
    GROUP BY BussinessPartnerMaster.BP_ID,SalesOrder.[SECTION],
    BussinessPartnerMaster.BILL_BP_ID,BussinessPartnerMaster.PAY_MONTH,
    BussinessPartnerMaster.PAY_DAY