T-SQL cursor does not produce expected result

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 . The data is always the same.

SELECT statement is ok. I do not understand why. Can anyone see the problem?

Answer 1

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

Now, here is the problem: 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 2

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

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 added a selection of the next line, before the END character