T-SQL cursor problem

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?

Answer 1

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.

Answer 2

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; 

Answer 3

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