SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 cursors did not work
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

musa88
Starting Member

2 Posts

Posted - 12/13/2012 :  04:23:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/13/2012 :  04:59:07  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
I notice there is no order by on the cursor which is one area where defaults have changed.
Otherwise add some code to show what the cursor is happening so you can see where the issue is.

==========================================
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000