January 31, 2012 at 3:25 pm
Hi
I have a cursor and I am trying to understand it. I have a couple of questions. here is the beginning of the cursor
DECLARE Cur1 CURSOR FAST_FORWARD FOR
SELECT fk_property_id, date_property_event, date_age_out
FROM stg_mddr_assignment
ORDER BY fk_property_id, date_property_event, date_age_out
DECLARE @pid int, @date_start datetime
DECLARE @pid_A int, @date_start_A datetime, @date_end_A datetime
DECLARE @pid_B int, @date_start_B datetime, @date_end_B datetime
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @pid_A, @date_start_A, @date_end_A
FETCH NEXT FROM Cur1 INTO @pid_B, @date_start_B, @date_end_B
Question:
FETCH NEXT FROM Cur1 INTO @pid_A, @date_start_A, @date_end_A
FETCH NEXT FROM Cur1 INTO @pid_B, @date_start_B, @date_end_B
When the sql is executed for Cur1
does this contain the same rows or does the first fetch get row 1 and puts row 1 data into _A variables and the second fetch gets row 2 data and puts it into variables _B
or do they both contain the same data i.e. Row1
Thanks in advance
January 31, 2012 at 3:45 pm
zwheeler (1/31/2012)
HiQuestion:
FETCH NEXT FROM Cur1 INTO @pid_A, @date_start_A, @date_end_A
FETCH NEXT FROM Cur1 INTO @pid_B, @date_start_B, @date_end_B
When the sql is executed for Cur1
does this contain the same rows or does the first fetch get row 1 and puts row 1 data into _A variables and the second fetch gets row 2 data and puts it into variables _B
or do they both contain the same data i.e. Row1
Thanks in advance
I think I would cry in public if I found this cursor on my system, but to answer your direct question: pid_A will contain data from Row 1, pid_B will contain data from Row 2. If looped until FETCH_STATUS shows EoR, A's will contain the odd rows, B's will contain the evens.
Fetch Next will always push the cursor to the next row, then put the columns into the defined variables.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 31, 2012 at 3:54 pm
zwheeler (1/31/2012)
HiI have a cursor and I am trying to understand it. I have a couple of questions. here is the beginning of the cursor
DECLARE Cur1 CURSOR FAST_FORWARD FOR
SELECT fk_property_id, date_property_event, date_age_out
FROM stg_mddr_assignment
ORDER BY fk_property_id, date_property_event, date_age_out
DECLARE @pid int, @date_start datetime
DECLARE @pid_A int, @date_start_A datetime, @date_end_A datetime
DECLARE @pid_B int, @date_start_B datetime, @date_end_B datetime
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @pid_A, @date_start_A, @date_end_A
FETCH NEXT FROM Cur1 INTO @pid_B, @date_start_B, @date_end_B
Question:
FETCH NEXT FROM Cur1 INTO @pid_A, @date_start_A, @date_end_A
FETCH NEXT FROM Cur1 INTO @pid_B, @date_start_B, @date_end_B
When the sql is executed for Cur1
does this contain the same rows or does the first fetch get row 1 and puts row 1 data into _A variables and the second fetch gets row 2 data and puts it into variables _B
or do they both contain the same data i.e. Row1
Thanks in advance
This is actually a pretty easy one to check for yourself by throwing in a couple of print statements 😀
February 1, 2012 at 8:50 am
I think I would cry in public if I found this cursor on my system
+1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply