Hi all

I'm starting to get my head round stored procedures (normally I write ASP pages for everything!)

This stored procedure examines the end date in a particular table and where it has lapsed it updates both itself and a child table to roll a contract on.

If Exec this in query analyser it gives the correct results as long as I comment out the two update statements if not all it does it run one record then stop.

Appreciate if some can give me a clue as to what I've got wrong

Thanks
Graham

CREATE procedure dbo.cascade_hire_end as

set nocount off
declare @rn int
declare @drn int
declare @hire_end datetime
declare @datetime datetime
declare @newdate datetime
select @datetime=getdate()
declare @vehicle_id nvarchar(12)
declare @driver_name nvarchar(255)
declare @alloc int

-- Declare the cursor
declare vehicles_cursor cursor local for
     select record_number,vehicle_id,driver_name,driver_record _number,hire_end from vehicles where archive_status<>'A' and depot_id<>'DEHIRED' and left(vehicle_status,1)<>'D' and (len(ltrim(quote_ref))=0 or quote_ref is null) and datediff(d,vehicles.hire_end,getdate())>0 and hire_end<>'1800/01/01' and hire_end is not null order by vehicle_id

open vehicles_cursor
fetch next from vehicles_cursor into @rn,@vehicle_id,@driver_name,@drn,@hire_end

while @@fetch_status=0
begin
-- loop
    fetch next from vehicles_cursor into @rn,@vehicle_id,@driver_name,@drn,@hire_end
    select @newdate=@hire_end

    while datediff(d,@newdate,getdate())>0
    begin
        select @newdate=dateadd(m,1,@newdate)

    end

    update platinum_sql.dbo.vehicles set changed_by='Cascade',changed_date=getdate(),hire_e nd=@newdate where record_number=@rn
    update platinum_sql.dbo.allocations set changed_by='Cascade',changed_date=getdate(),end_da te=@newdate where vehicle_record_number=@rn and driver_record_number=@drn and end_date=@hire_end
    print 'Vehicle= '+@vehicle_id+' driven by '+@driver_name+' due to expire on '+cast(@hire_end as nvarchar)+' now extended to ' +cast(@newdate as nvarchar)

end
-- tidy
close vehicles_cursor
deallocate vehicles_cursor
GO

Hard to say without knowing the data that is involved. But I would try defining the cursor as dynamic e.g. declare vehicles_cursor cursor local dynamic for

Hiya

No that didn't do it!

Just seems weird that it works fine if remove the update lines, query analyser shows me the records that have expired and what the contract needs to be extended to so its back in date,

I've also put @@error checks since I posted and QA does not report problems

Oh well suppose I'll have to put the code back into the ASP page for now.

If you come across anything else would appreciate a post.

Regards

I know this is not the problem but you do have a bug in the code in that you open the cursor and do a fetch next. You then go into the loop and do a further fetch next which means the first record in the cursor is not processed.

It is also worth mentioning that cursors are to be avoided if you can. They can makes things go very slow.