Cursor repeats row
-
I have a Cursor that is repeating the last row it finds. When I run the select statement that the "Declare Cursor For" has I only get 15 rows. But when I run the below statement I get 16 rows with the last one repeated.
Anyone have a suggestion?
Why would the last one repeat?
Seems like the general opionion is to not use Cursors. Anyone have a suggestion as to what I could use in place of this?
Declare @bill_ID int
Declare Bill_Cursor Cursor for
select id from v_View a JOIN t_Table b on a.bill_id = b.Id and entered > '2006-06-01 00:00:00'
open Bill_CURSOR
fetch next from Bill_CURSOR into @Bill_ID
Print @Bill_ID
while @@FETCH_STATUS = 0
begin
fetch next from Bill_CURSOR into @Bill_ID
Print @Bill_ID
end
close Bill_CURSOR
deallocate BIll_CURSOR
-
What are you planning to do with the cursor? the above can be substituted for nothing at all.
* Noel -
Noel,
THanks for the response. I'm generating a list of Bill_IDs that are from the view. With this list I am going to autogenerate invoices and email these customers. I will execute an SP that will create and email the invoice for each Bill_ID that is in that list.
-
Your 15th trip throught the while loop gets a row and prints. Your net loop through doesn't get a row and setus the status, but you print regardless so it looks like you're getting the last row twice. You need a check for fetch_status befor the print.
And then again, I might be wrong ...
David Webb -
If the purpose is to send emails and you are planing to use xp_sendmail for that you will need the cursor!
Like David said change the position of your print statement in the While loop
Declare @bill_ID int
Declare Bill_Cursor Cursor for
select id from v_View a JOIN t_Table b on a.bill_id = b.Id and entered > '2006-06-01 00:00:00'
open Bill_CURSOR
fetch next from Bill_CURSOR into @Bill_ID
--
Print @Bill_IDwhile @@FETCH_STATUS = 0
begin
Print @Bill_ID
fetch next from Bill_CURSOR into @Bill_ID
end
close Bill_CURSOR
deallocate BIll_CURSOR
* Noel -
Removing the first print command and moving the second like you said Noel worked. I'm gonna have to play around with cursors a little more. Thanks all for the responses.
-
you can use a table variable
DECLARE @RowCount BIGINT --tracker
DECLARE @RecCount BIGINT --record counter
Declare @bill_ID bigint
DECLARE @tbl TABLE
(
[ref] [BIGINT] IDENTITY (1, 1) PRIMARY KEY NOT NULL
, bill_ID [BIGINT] NOT NULL
)
Insert into @tbl
select id from v_View a JOIN t_Table b on a.bill_id = b.Id and entered > '2006-06-01 00:00:00'
--get count of records
select @RecCount = count(*)
from @tbl
SELECT @RowCount = 0
WHILE @RowCount <= @RecCount
BEGIN
SELECT@bill_ID = bill_ID
FROM@tbl
WHEREref = @RowCount
Print str(@Bill_ID)
SELECT@RowCount = @RowCount + 1
END
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply