Using fetch and cursor in a while loop

Question

Post reply

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*******

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

chalam87 Right there with Babe Points: 771 More actions · Answer 1

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 · Answer 2

@DJ

SSCommitted

Points: 1989

May 27, 2013 at 4:32 am

#1618738

Hi.

Thanks that works.

BR

Dan

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.