--将采购入库单传递到中间库(委外订单不受约束) if exists (select * from sysobjects where id = object_id('dbo.usp_transrdrecord01') ) drop procedure dbo.usp_transrdrecord01 go Create PROCEDURE usp_transrdrecord01 @v_start datetime, --开始时间 @v_end datetime --结束时间 as select Id into #t_01 from RdRecord01 r where (dnmaketime>=@v_start and dnmaketime <= @v_end and ISNULL(cDefine5,0) = 0) or (exists (select Id from RdRecord01 where cBusType='委外加工' and ID=r.ID) ) if @@ROWCOUNT=0 return delete from wtu..rdrecord01 where exists (select id from #t_01 where ID=wtu..rdrecord01.ID) delete from wtu..rdrecords01 where exists (select id from #t_01 where ID=wtu..rdrecords01.ID) ----定义游标(解决排它锁),让游标指向sql查询的结果 --declare cursors cursor for -- select id from #t_01 --open cursors -- declare @num int -- fetch next from cursors into @num -- while(@@FETCH_STATUS=0) -- begin -- delete from wtu..RdRecord01 where ID=@num -- delete from wtu..rdrecords01 where ID=@num -- fetch next from cursors into @num -- end --close cursors --deallocate cursors --释放游标,释放完不能open insert into wtu..RdRecord01 (ID, bRdFlag, cVouchType, cBusType, cSource, cBusCode, cWhCode, dDate, cCode, cRdCode, cDepCode, cPersonCode, cPTCode, cSTCode, cCusCode, cVenCode, cOrderCode, cARVCode, cBillCode, cDLCode, cProBatch, cHandler, cMemo, bTransFlag, cAccounter, cMaker, cDefine1, cDefine2, cDefine3, cDefine4, cDefine5, cDefine6, cDefine7, cDefine8, cDefine9, cDefine10, dKeepDate, dVeriDate, bpufirst, biafirst, iMQuantity, dARVDate, cChkCode, dChkDate, cChkPerson, VT_ID, bIsSTQc, cDefine11, cDefine12, cDefine13, cDefine14, cDefine15, cDefine16, gspcheck, ipurorderid, ipurarriveid, iarriveid, isalebillid, iTaxRate, iExchRate, cExch_Name, bOMFirst, bFromPreYear, bIsLsQuery, bIsComplement, iDiscountTaxType, ireturncount, iverifystate, iswfcontrolled, cModifyPerson, dModifyDate, dnmaketime, dnmodifytime, dnverifytime, bredvouch, cVenPUOMProtocol, dCreditStart, iCreditPeriod, dGatheringDate, bCredit, iFlowId, cPZID, cSourceLs, cSourceCodeLs, iPrintCount, csysbarcode, cCurrentAuditor, cCheckSignFlag, cGCRouteCode ) select r.ID, bRdFlag, cVouchType, cBusType, cSource, cBusCode, cWhCode, dDate, cCode, cRdCode, cDepCode, cPersonCode, cPTCode, cSTCode, cCusCode, cVenCode, cOrderCode, cARVCode, cBillCode, cDLCode, cProBatch, cHandler, cMemo, bTransFlag, cAccounter, cMaker, cDefine1, cDefine2, cDefine3, cDefine4, cDefine5, cDefine6, cDefine7, cDefine8, cDefine9, cDefine10, dKeepDate, dVeriDate, bpufirst, biafirst, iMQuantity, dARVDate, cChkCode, dChkDate, cChkPerson, VT_ID, bIsSTQc, cDefine11, cDefine12, cDefine13, cDefine14, cDefine15, cDefine16, gspcheck, ipurorderid, ipurarriveid, iarriveid, isalebillid, iTaxRate, iExchRate, cExch_Name, bOMFirst, bFromPreYear, bIsLsQuery, bIsComplement, iDiscountTaxType, ireturncount, iverifystate, iswfcontrolled, cModifyPerson, dModifyDate, dnmaketime, dnmodifytime, dnverifytime, bredvouch, cVenPUOMProtocol, dCreditStart, iCreditPeriod, dGatheringDate, bCredit, iFlowId, cPZID, cSourceLs, cSourceCodeLs, iPrintCount, csysbarcode, cCurrentAuditor, cCheckSignFlag, cGCRouteCode from RdRecord01 r where exists(select ID from #t_01 t where t.ID=r.ID) --------------------------------------------- insert into wtu..RdRecords01 (AutoID, ID, cInvCode, iNum, iQuantity, iUnitCost, iPrice, iAPrice, iPUnitCost, iPPrice, cBatch, cVouchCode, cInVouchCode, cinvouchtype, iSOutQuantity, iSOutNum, cFree1, cFree2, iFlag, dSDate, iTax, iSQuantity, iSNum, iMoney, iFNum, iFQuantity, dVDate, cPosition, cDefine22, cDefine23, cDefine24, cDefine25, cDefine26, cDefine27, cItem_class, cItemCode, iPOsID, fACost, cName, cItemCName, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, cBarCode, iNQuantity, iNNum, cAssUnit, dMadeDate, iMassDate, cDefine28, cDefine29, cDefine30, cDefine31, cDefine32, cDefine33, cDefine34, cDefine35, cDefine36, cDefine37, iCheckIds, cBVencode, chVencode, bGsp, cGspState, iArrsId, cCheckCode, iCheckIdBaks, cRejectCode, iRejectIds, cCheckPersonCode, dCheckDate, iOriTaxCost, iOriCost, iOriMoney, iOriTaxPrice, ioriSum, iTaxRate, iTaxPrice, iSum, bTaxCost, cPOID, cMassUnit, iMaterialFee, iProcessCost, iProcessFee, dMSDate, iSMaterialFee, iSProcessFee, iOMoDID, strContractId, strCode, bChecked, bRelated, iOMoMID, iMatSettleState, iBillSettleCount, bLPUseFree, iOriTrackID, coritracktype, cbaccounter, dbKeepDate, bCosting, iSumBillQuantity, bVMIUsed, iVMISettleQuantity, iVMISettleNum, cvmivencode, iInvSNCount, cwhpersoncode, cwhpersonname, impcost, iIMOSID, iIMBSID, cbarvcode, dbarvdate, iinvexchrate, comcode, strContractGUID, iExpiratDateCalcu ) select AutoID, r.ID, cInvCode, iNum, iQuantity, iUnitCost, iPrice, iAPrice, iPUnitCost, iPPrice, cBatch, cVouchCode, cInVouchCode, cinvouchtype, iSOutQuantity, iSOutNum, cFree1, cFree2, iFlag, dSDate, iTax, iSQuantity, iSNum, iMoney, iFNum, iFQuantity, dVDate, cPosition, cDefine22, cDefine23, cDefine24, cDefine25, cDefine26, cDefine27, cItem_class, cItemCode, iPOsID, fACost, cName, cItemCName, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, cBarCode, iNQuantity, iNNum, cAssUnit, dMadeDate, iMassDate, cDefine28, cDefine29, cDefine30, cDefine31, cDefine32, cDefine33, cDefine34, cDefine35, cDefine36, cDefine37, iCheckIds, cBVencode, chVencode, bGsp, cGspState, iArrsId, cCheckCode, iCheckIdBaks, cRejectCode, iRejectIds, cCheckPersonCode, dCheckDate, iOriTaxCost, iOriCost, iOriMoney, iOriTaxPrice, ioriSum, iTaxRate, iTaxPrice, iSum, bTaxCost, cPOID, cMassUnit, iMaterialFee, iProcessCost, iProcessFee, dMSDate, iSMaterialFee, iSProcessFee, iOMoDID, strContractId, strCode, bChecked, bRelated, iOMoMID, iMatSettleState, iBillSettleCount, bLPUseFree, iOriTrackID, coritracktype, cbaccounter, dbKeepDate, bCosting, iSumBillQuantity, bVMIUsed, iVMISettleQuantity, iVMISettleNum, cvmivencode, iInvSNCount, cwhpersoncode, cwhpersonname, impcost, iIMOSID, iIMBSID, cbarvcode, dbarvdate, iinvexchrate, comcode, strContractGUID, iExpiratDateCalcu from RdRecords01 r where exists(select ID from #t_01 t where t.ID=r.ID) --释放临时表 drop table #t_01