March 1, 2014 at 8:34 pm
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.
01095E97E0310
01095E980C400
01095E99F2160
01095E9AA1500
01095E9AA6100
What is staring me in the face here?
I'd love some help.
Cheers
March 1, 2014 at 9:57 pm
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.
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;
March 2, 2014 at 11:02 am
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
Change is inevitable... Change for the better is not.
March 3, 2014 at 1:47 am
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
March 3, 2014 at 6:25 am
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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply