musa88
Starting Member
2 Posts |
Posted - 2012-12-13 : 04:23:40
|
Dear Expert,i have cursor that work out at sql server 2000, but when use it at sql server 2012 it didn't work.as below :ALTER PROC abc @RI_ID intASDECLARE @BatNbr char(10),@TRcd1 char(15),@TRName1 char(30),@TRprov1 float,@TRvouc1 float,@TRcd2 char(15),@TRName2 char(30),@TRprov2 float,@TRvouc2 float,@TRcd3 char(15),@TRName3 char(30),@TRprov3 float,@TRvouc3 float,@InvcNbr char(15),@Perpost char(6)Select @Perpost = BegPerNbr From RptRuntime Where RI_ID = @RI_IDDelete xtWrkTransferStatus Where RI_ID = @RI_IDInsert Into xtWrkTransferStatusSelect @RI_ID,a.BatNbr,'','',0,'',0,'','',0,'',0,'','',0,'',0From POReceipt a,Batch b,xtPurchOrdAdd cWhere a.BatNbr = b.BatNbrAnd b.Module = 'PO'And a.PONbr = c.PONbrAnd b.Perpost = @PerpostAnd a.RcptType = 'R'And c.POType = '3'Insert Into xtWrkTransferStatusSelect @RI_ID,a.ShipperID,a.ShipviaID,left(descr,30),Isnull(c.TRCost,0),'',0,'','',0,'',0,'','',0,'',0From SOShipHeader a Inner Join Shipvia b ona.ShipviaID = b.ShipviaIDLeft Outer Join xtShipEtaAct c ona.ShipperID = c.ShipperIDWhere Perpost = @PerpostAnd a.ShipperID like 'SBR%'DECLARE myCursor CURSOR FORSelect BatNbr,MftMp,isnull(left(b.name,30),''),TrCost1,MptJSp,isnull(left(c.name,30),''),TrCost2,JSptWh,isnull(left(d.name,30),''),TrCost3From xtTRStatus a left Outer Join vendor b Ona.MftMp = b.VendIDleft Outer Join vendor c Ona.MptJSp = c.VendIDleft Outer Join vendor d Ona.JSptWh = d.VendIDOpen myCursorFetch Next From myCursor Into @BatNbr,@TRcd1,@TRname1,@TRprov1,@TRcd2,@TRname2,@TRprov2,@TRcd3,@TRname3,@TRprov3While (@@Fetch_Status=0)BeginUpdate xtWrkTransferStatusSet TRcd1 = @TRcd1,TRname1 = @TRname1,TRprov1 = TRprov1 + @TRprov1,TRcd2 = @TRcd2,TRname2 = @TRname2,TRprov2 = TRprov2 + @TRprov2,TRcd3 = @TRcd3,TRname3 = @TRname3,TRprov3 = TRprov3 + @TRprov3Where BatNbr = @BatNbrAnd RI_ID = @RI_IDFetch Next From myCursor Into @BatNbr,@TRcd1,@TRname1,@TRprov1,@TRcd2,@TRname2,@TRprov2,@TRcd3,@TRname3,@TRprov3EndClose myCursorDeallocate myCursorDECLARE myCursor CURSOR FORselect c.BatNbr,a.VendID,b.InvcNbr,a.CuryTranAmtfrom aptran a,apdoc b,poreceipt cwhere a.BatNbr = b.BatNbrand a.RefNbr = b.RefNbrand a.user6 = c.ponbrand c.rcptType = 'R'Open myCursorFetch Next From myCursor Into @BatNbr,@TRcd1,@InvcNbr,@TRvouc1While (@@Fetch_Status=0)BeginUpdate xtWrkTransferStatusSet TRinv1 = @InvcNbr,TRvouc1 = TRvouc1 + @TRvouc1Where BatNbr = @BatNbrAnd TRcd1 = @TRcd1If not Exists(select * from xtWrkTransferStatus Where BatNbr = @BatNbr And TRcd1 = @TRcd1)BeginUpdate xtWrkTransferStatusSet TRinv2 = @InvcNbr,TRvouc2 = TRvouc2 + @TRvouc1Where BatNbr = @BatNbrAnd TRcd2 = @TRcd1EndIf not Exists(select * from xtWrkTransferStatus Where BatNbr = @BatNbr And TRcd2 = @TRcd1)BeginUpdate xtWrkTransferStatusSet TRinv3 = @InvcNbr,TRvouc3 = TRvouc3 + @TRvouc1Where BatNbr = @BatNbrAnd TRcd3 = @TRcd1EndFetch Next From myCursor Into @BatNbr,@TRcd1,@InvcNbr,@TRvouc1EndClose myCursorDeallocate myCursorthe cursor is used to update an view table xtWrkTransferStatus.but when check this table there is no update..Please help me to know the problem and the solution.Thanks for your attention.BR,musa |
|