December 23, 2014 at 12:13 pm
Hi, I'm trying to build a simple cursor to understand how they work. From the temp table, I would like to print out the values of the table, when I run my cursor it just keeps running the output of the first row infinitely. I just want it to print out the 7 rows in the table ...
IF OBJECT_ID('TempDB..#tTable','U') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable
(
tID int,
minValue int,
maxValue int,
tName varchar(25)
)
insert into #tTable
(tID, MinValue, MaxValue, tName)
SELECT '1','0','3','0-3 Mths' UNION ALL
SELECT '2','3','6','3-6 Mths' UNION ALL
SELECT '3','6','9','6-9 Mths' UNION ALL
SELECT '4','9','12','9-12 Mths' UNION ALL
SELECT '5','12','18','12-18 Mths' UNION ALL
SELECT '6','18','24','18-24 Mths' UNION ALL
SELECT '7','24','9999','24+ Mths'
select * from #tTable
declare @tid as int;
declare @min-2 as int;
declare @max-2 as int;
declare @TN as varchar(25);
declare @otCursor as cursor;
set @otCursor = cursor for
select TenureID, MinMonths, MaxMonths, TenureName from #tTable;
open @otCursor;
fetch next from @otCursor into @tid,@min,@max,@tn
while @@fetch_status = 0
begin
cast(@tid as varchar(50)) + ' ' +
cast(@min as varchar(50)) + ' ' + cast(@max as varchar(50)) + ' ' +
@TN;
end
close @otCursor
deallocate @otCursor
December 23, 2014 at 12:37 pm
I believe what you need is a FETCH NEXT just before the END in your WHILE loop:
while @@fetch_status = 0
begin
cast(@tid as varchar(50)) + ' ' +
cast(@min as varchar(50)) + ' ' + cast(@max as varchar(50)) + ' ' +
@TN;
FETCH NEXT FROM @otCursor into @tid,@min,@max,@tn
end
Without the FETCH NEXT in the loop, the cursor won't retrieve any more data from the target dataset, and you'll just keep repeating the loop, since @@FETCH_STATUS will never hit the end-of-dataset status level.
- 😀
December 23, 2014 at 12:38 pm
Thank you!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply