T-SQL cursor not producing expected output

Question

GO
SET NOCOUNT ON;

DECLARE
@idAdvertisements int,
@Name nvarchar(255),
@Description nvarchar(500),
@DepartureDate datetime,
@Cities_idCities int,
@Areas_idAreas int,
@Countries_idCountries int,
@Agencies_idAgencies int,
@Url nvarchar(1000),
@Price decimal(6, 2),
@HollidayDuration int,
@BookingDate datetime;

DECLARE ad_cursor CURSOR
   FOR SELECT idAdvertisements
             ,Name
             ,Description
             ,DepartureDate
             ,Cities_idCities
             ,Areas_idAreas
             ,Countries_idCountries
             ,Agencies_idAgencies
             ,Url
             ,Price
             ,HollidayDuration
             ,BookingDate
      FROM Advertisements;

OPEN ad_cursor;
FETCH NEXT FROM ad_cursor
INTO @idAdvertisements
    ,@Name
    ,@Description
    ,@DepartureDate
    ,@Cities_idCities
    ,@Areas_idAreas
    ,@Countries_idCountries
    ,@Agencies_idAgencies
    ,@Url
    ,@Price
    ,@HollidayDuration
    ,@BookingDate;

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT ' ';
   PRINT @idAdvertisements;
   --PRINT @Name;
   --PRINT @Description;
   --PRINT @DepartureDate;
   --PRINT @Cities_idCities;

END
CLOSE ad_cursor;

But I always get 1, 1, 1, 1, 1. Data are always the same.

SELECT statement is OK. I don't understand why. Can someone see the problem?

Why even bother with a CURSOR?? If you must use a CURSOR, then at least define it as CURSOR FAST_FORWARD to speed it up a little. But the best choice would be to avoid the cursor alltogether - in 90% of the cases, you can, too! — marc_s
– marc_s, Commented Jul 22, 2010 at 12:04
more code inside so curosr is needed. I delete some code for better view. — senzacionale
– senzacionale, Commented Jul 22, 2010 at 12:09
Let me increase mark_s's 90% with 9. @senzacionale: More code does not mean cursors are more reasonable. — Florian Reischl
– Florian Reischl, Commented Jul 22, 2010 at 12:21
suggest you read this before trying to write another cursor, cursor are the worst way to do things in many databases! wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them — HLGEM
– HLGEM, Commented Jul 22, 2010 at 18:11

Raj More · Accepted Answer · 2010-07-22 12:04:50Z

cursors are Evil.. eVIL.. eVil.. stay away from them

Now, here is the issue - you are not moving the cursor forward..

DECLARE ad_cursor CURSOR
   FOR SELECT idAdvertisements, Name, Description, DepartureDate, Cities_idCities, Areas_idAreas,
    Countries_idCountries, Agencies_idAgencies, Url, Price, HollidayDuration, BookingDate FROM Advertisements;

OPEN ad_cursor;
FETCH NEXT FROM ad_cursor
INTO @idAdvertisements, @Name, @Description, @DepartureDate, @Cities_idCities, @Areas_idAreas,
@Countries_idCountries, @Agencies_idAgencies, @Url, @Price, @HollidayDuration, @BookingDate;

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT ' ';
   PRINT @idAdvertisements;
   --PRINT @Name;
   --PRINT @Description;
   --PRINT @DepartureDate;
   --PRINT @Cities_idCities;

    FETCH NEXT FROM ad_cursor
    INTO @idAdvertisements, @Name, @Description, @DepartureDate, @Cities_idCities, @Areas_idAreas,
    @Countries_idCountries, @Agencies_idAgencies, @Url, @Price, @HollidayDuration, @BookingDate;


END
CLOSE ad_cursor;

AakashM · Accepted Answer · 2010-07-22 12:03:31Z

3

The code as you have it here will loop infinitely. You need another FETCH at the end of the body of the WHILE loop, otherwise @@FETCH_STATUS will never change.

answered Jul 22, 2010 at 12:03

AakashM

63.5k17 gold badges154 silver badges190 bronze badges

Comments

Sandeep Kumar M · Accepted Answer · 2010-07-22 12:07:04Z

2

OPEN ad_cursor;
FETCH NEXT FROM ad_cursor
INTO @idAdvertisements, @Name, @Description, @DepartureDate, @Cities_idCities, @Areas_idAreas,
@Countries_idCountries, @Agencies_idAgencies, @Url, @Price, @HollidayDuration, @BookingDate;

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT ' ';
   PRINT @idAdvertisements;
   --PRINT @Name;
   --PRINT @Description;
   --PRINT @DepartureDate;
   --PRINT @Cities_idCities;
FETCH NEXT FROM ad_cursor
INTO @idAdvertisements, @Name, @Description, @DepartureDate, @Cities_idCities, @Areas_idAreas,
@Countries_idCountries, @Agencies_idAgencies, @Url, @Price, @HollidayDuration, @BookingDate;
END
CLOSE ad_cursor;

You have add fetch the next row, just before the END

answered Jul 22, 2010 at 12:07

Sandeep Kumar M

3,8513 gold badges41 silver badges60 bronze badges

Collectives™ on Stack Overflow

T-SQL cursor not producing expected output

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related