cursor did not work at sql server 2012

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

cursor did not work at sql server 2012

Author

Topic

musa88
Starting Member

2 Posts

Posted - 2012-12-13 : 04:09:35

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 int
AS

DECLARE @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_ID

Delete xtWrkTransferStatus Where RI_ID = @RI_ID

Insert Into xtWrkTransferStatus
Select @RI_ID,a.BatNbr,'','',0,'',0,'','',0,'',0,'','',0,'',0
From POReceipt a,Batch b,xtPurchOrdAdd c
Where a.BatNbr = b.BatNbr
And b.Module = 'PO'
And a.PONbr = c.PONbr
And b.Perpost = @Perpost
And a.RcptType = 'R'
And c.POType = '3'

Insert Into xtWrkTransferStatus
Select @RI_ID,a.ShipperID,a.ShipviaID,left(descr,30),Isnull(c.TRCost,0),'',0,'','',0,'',0,'','',0,'',0
From SOShipHeader a Inner Join Shipvia b on
a.ShipviaID = b.ShipviaID
Left Outer Join xtShipEtaAct c on
a.ShipperID = c.ShipperID
Where Perpost = @Perpost
And a.ShipperID like 'SBR%'

DECLARE myCursor CURSOR FOR
Select BatNbr,MftMp,isnull(left(b.name,30),''),TrCost1,MptJSp,isnull(left(c.name,30),''),TrCost2,JSptWh,isnull(left(d.name,30),''),TrCost3
From xtTRStatus a left Outer Join vendor b On
a.MftMp = b.VendID
left Outer Join vendor c On
a.MptJSp = c.VendID
left Outer Join vendor d On
a.JSptWh = d.VendID

Open myCursor
Fetch Next From myCursor Into @BatNbr,@TRcd1,@TRname1,@TRprov1,@TRcd2,@TRname2,@TRprov2,@TRcd3,@TRname3,@TRprov3
While (@@Fetch_Status=0)
Begin
Update xtWrkTransferStatus
Set TRcd1 = @TRcd1,
TRname1 = @TRname1,
TRprov1 = TRprov1 + @TRprov1,
TRcd2 = @TRcd2,
TRname2 = @TRname2,
TRprov2 = TRprov2 + @TRprov2,
TRcd3 = @TRcd3,
TRname3 = @TRname3,
TRprov3 = TRprov3 + @TRprov3
Where BatNbr = @BatNbr
And RI_ID = @RI_ID

Fetch Next From myCursor Into @BatNbr,@TRcd1,@TRname1,@TRprov1,@TRcd2,@TRname2,@TRprov2,@TRcd3,@TRname3,@TRprov3
End
Close myCursor
Deallocate myCursor

DECLARE myCursor CURSOR FOR
select c.BatNbr,a.VendID,b.InvcNbr,a.CuryTranAmt
from aptran a,apdoc b,poreceipt c
where a.BatNbr = b.BatNbr
and a.RefNbr = b.RefNbr
and a.user6 = c.ponbr
and c.rcptType = 'R'
Open myCursor
Fetch Next From myCursor Into @BatNbr,@TRcd1,@InvcNbr,@TRvouc1
While (@@Fetch_Status=0)
Begin
Update xtWrkTransferStatus
Set TRinv1 = @InvcNbr,
TRvouc1 = TRvouc1 + @TRvouc1
Where BatNbr = @BatNbr
And TRcd1 = @TRcd1

If not Exists(select * from xtWrkTransferStatus Where BatNbr = @BatNbr And TRcd1 = @TRcd1)
Begin
Update xtWrkTransferStatus
Set TRinv2 = @InvcNbr,
TRvouc2 = TRvouc2 + @TRvouc1
Where BatNbr = @BatNbr
And TRcd2 = @TRcd1

End

If not Exists(select * from xtWrkTransferStatus Where BatNbr = @BatNbr And TRcd2 = @TRcd1)
Begin
Update xtWrkTransferStatus
Set TRinv3 = @InvcNbr,
TRvouc3 = TRvouc3 + @TRvouc1
Where BatNbr = @BatNbr
And TRcd3 = @TRcd1

End

Fetch Next From myCursor Into @BatNbr,@TRcd1,@InvcNbr,@TRvouc1
End
Close myCursor
Deallocate myCursor

the 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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-13 : 05:00:22

Duplicate
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181425

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources