CODEWITHSUNDEEP
t-sql
senzacionale
senzacionale

Reputation: 20936

T-SQL cursor not producing expected output

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?

Upvotes: 4

Views: 461

Answers (3)

Sandeep Kumar M
Sandeep Kumar M

Reputation: 3851

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

Upvotes: 2

Raj More
Raj More

Reputation: 48048

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;

Upvotes: 3

AakashM
AakashM

Reputation: 63378

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.

Upvotes: 3

Related Questions