Using fetch and cursor in a while loop

• @DJ

  SSCommitted

  Points: 1989

  More actions

  May 27, 2013 at 3:37 am

  #294513

  Hi

  I have a master table that has truck,lisens and startdate.

  Now I need to run this, one record at the time and use the trucK and lisens to get the stop date from another table.

  I have written the code below, but it does not print my truck id in the query section of the while.

  I will replace the print with my actual query of course, but I ran the code once with out any output and now it seems that the reason is because I do not get any truck and lisens from the id_cursor

  Any comments on what I am doing wrong?

  ******START******

  -- Declare the variables to store the values returned by FETCH.

  DECLARE @truck varchar(3), @min_lic varchar(12);

  DECLARE id_cursor CURSOR FOR

  SELECT truck,min_lic FROM KPIPlukkTidStart;

  OPEN id_cursor;

  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

  WHILE @@FETCH_STATUS = 0

  BEGIN

  --Query

  --Try to print the parameters

  PRINT 'The truck : ' + @truck

  FETCH NEXT FROM id_cursor

  INTO @truck, @min_lic;

  END

  CLOSE id_cursor;

  DEALLOCATE id_cursor;

  *****STOP*******

• chalam87

  Right there with Babe

  Points: 771

  More actions

  May 27, 2013 at 3:54 am

  #1618725

  Hi try this.

  ******START******

  -- Declare the variables to store the values returned by FETCH.

  DECLARE @truck varchar(3), @min_lic varchar(12);

  DECLARE id_cursor CURSOR FOR

  SELECT truck,min_lic FROM KPIPlukkTidStart;

  OPEN id_cursor;

  Fetch Next From id_cursor into @truck,@min_lic

  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

  WHILE @@FETCH_STATUS = 0

  BEGIN

  --Query

  --Try to print the parameters

  PRINT 'The truck : ' + @truck

  FETCH NEXT FROM id_cursor

  INTO @truck, @min_lic;

  END

  CLOSE id_cursor;

  DEALLOCATE id_cursor;

  *****STOP*******

  --chalam

• @DJ

  SSCommitted

  Points: 1989

  More actions

  May 27, 2013 at 4:32 am

  #1618738

  Hi.

  Thanks that works.

  BR

  Dan

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 27, 2013 at 7:41 am

  #1618789

  You do realize that there is probably a set-based solution to your problem so that a cursor solution is not needed. If you would provide the details of the problem you are actually trying to solve I am sure someone will help you.