Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trouble looping through a cursor Expand / Collapse
Author
Message
Posted Saturday, March 1, 2014 8:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:21 AM
Points: 7, Visits: 22
Greetings.

I am having a problem with a simple cursor.

It is working but it is processing the last record in the cursor twice and driving me crazy.

This is the code that I have simplified to demonstrate the issue.
DECLARE @txt_RFID varchar(50)
DECLARE @txt_PartNumWM varchar(50)
DECLARE @txt_CurrentStep varchar(50)
DECLARE @ls_querystring varchar(4000)
DECLARE @ls_date varchar(20)
DECLARE @ls_time varchar(20)
DECLARE @ln_maxnumber numeric
DECLARE @ls_maxnumber varchar(10)
DECLARE @mycounter numeric

DECLARE FIXDNUSHEET CURSOR FOR

SELECT RFID,CurrentStep
FROM [Traceability].[dbo].[Travelcard]
WHERE
(PartNumWM = 'WM-005'
OR PartNumWM = 'WM-008'
OR PartNumWM = 'WM-010')
AND
CurrentStep != 'Complete'
AND
CurrentStep != '000'
order by RFID;

set @mycounter = 0

OPEN FIXDNUSHEET

FETCH NEXT FROM FIXDNUSHEET INTO @txt_RFID,@txt_CurrentStep
set @mycounter = @mycounter + 1
print @txt_RFID + ' -----------------------'
print @mycounter
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM FIXDNUSHEET INTO @txt_RFID,@txt_CurrentStep
set @mycounter = @mycounter + 1
print @txt_RFID + ' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^'
print @mycounter
END

CLOSE FIXDNUSHEET;
DEALLOCATE FIXDNUSHEET;



This is the result of the last 5 records at the end of the cursor processing. You can see that the last record is duplicated.

01095E980C ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
129
01095E99F2 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
130
01095E9AA1 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
131
01095E9AA6 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
132
01095E9AA6 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
133


This is a list of the last 5 records in the cursor showing that there is no duplicate of the last record. I obtained this by running the select query within the script.


01095E97E0 310
01095E980C 400
01095E99F2 160
01095E9AA1 500
01095E9AA6 100


What is staring me in the face here?
I'd love some help.

Cheers
Post #1546683
Posted Saturday, March 1, 2014 9:57 PM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:21 AM
Points: 7, Visits: 22
Greetings.

I've found the issue.

I did the fetch and the processing in the wrong order. This means that the value of @@FETCH_STATUS does not change to a value != 0 before it has processed the last record a second time.

Shifting the processing before the FETCH has resolved the issue.

I've placed the code change below.

[code = "plain"]
DECLARE @txt_RFID varchar(50)
DECLARE @txt_PartNumWM varchar(50)
DECLARE @txt_CurrentStep varchar(50)
DECLARE @ls_querystring varchar(4000)
DECLARE @ls_date varchar(20)
DECLARE @ls_time varchar(20)
DECLARE @ln_maxnumber numeric
DECLARE @ls_maxnumber varchar(10)
DECLARE @mycounter numeric

DECLARE FIXDNUSHEET CURSOR FOR

SELECT RFID,CurrentStep
FROM [CarbonRevTraceability].[dbo].[WheelTravelcard]
WHERE
(PartNumWM = 'WM-005'
OR PartNumWM = 'WM-008'
OR PartNumWM = 'WM-010')
AND
CurrentStep != 'Complete'
AND
CurrentStep != '000'
order by RFID;

set @mycounter = 0

OPEN FIXDNUSHEET

FETCH NEXT FROM FIXDNUSHEET INTO @txt_RFID,@txt_CurrentStep

WHILE @@FETCH_STATUS = 0
BEGIN
set @mycounter = @mycounter + 1
print @txt_RFID
print @mycounter
FETCH NEXT FROM FIXDNUSHEET INTO @txt_RFID,@txt_CurrentStep

END

CLOSE FIXDNUSHEET;
DEALLOCATE FIXDNUSHEET;
[/code]
Post #1546687
Posted Sunday, March 2, 2014 11:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
You've marked your own answer as the "worked for you" answer but it's not the correct answer. You don't need a cursor for this at all. The following code will do the same as your cursor except that it will be faster, easier to maintain, won't cause problems like your original problem, and will use fewer resources.

 SELECT RFID, MyCounter = ROW_NUMBER() OVER (ORDER BY RFID)
FROM Traceability.dbo.Travelcard
WHERE PartNumWM IN ('WM-005','WM-008','WM-010')
AND CurrentStep NOT IN ('Complete','000'
;



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1546713
Posted Monday, March 3, 2014 1:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:21 AM
Points: 7, Visits: 22
Thank you Jeff for responding.

I've run your code and it does do as you say and I guess in a way what I had asked.

The intention of the code that I was writing however was somewhat more involved than that which I posted. I removed all the working code to remove confusion and just concentrate on why it was processing the last record twice

The counter was just put there as part of my debugging.

What i have to do is add a record to another table based on the RFID that your script and mine extracts from the Travelcard table. In this record there is a range of constant strings. Values extracted from other tables. Calculated values from other tables and it needs to pick up from a record counter that is not based on a sequence.

So what I have done is placed an insert statement within the loop that gets the creates the record with all the requisite data elements.

I'm sure someone smarter and cleverer that me could write this in a single update statement but I chose a cursor because it was the easiest way that i could do it.

One again thanks

Peter
Post #1546815
Posted Monday, March 3, 2014 6:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
The cursor will become a performance and resource usage problem. Do you have someone on-site that could help you turn this into a proper "upsert"?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1546898
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse