CODEWITHSUNDEEP
sqlsql-server
ProgrammingBaKa
ProgrammingBaKa

Reputation: 373

SQL server cursor run twice

Suppose i have a cursor which find data to update,

declare @index int;
declare cursor1 cursor for
select table1_index from table where (table1_date < dateadd(dd, -365, getdate()))

open cursor1 
fetch next from cursor1 into @index
while @@fetch_status = 0
begin
  update table1
  set table1_field = SOMETHING
  where table1_index = @index

  if @ERROR = 0
  insert into audit_trail
  values(getdate(), table1_index)

  fetch next from cursor1 into @index
end
close cursor1 
deallocate cursor1

The above code is in a stored procedure and will be ran by a scheduler everyday (for example 12:00 am everyday).

My question is that if the scheduler ran the stored pro. in a time (for example, 17/06/2017 12:00 AM), and still running (for example in 17/06/2017 05:00 PM).

If i run the exact same code in (for example 17/06/2017 03:00 PM), will the cursor select out data from the table which is updated? Or data from table that was not updated by the scheduler?

Thanks a lot.

Upvotes: 0

Views: 527

Answers (1)

Tien Nguyen Ngoc
Tien Nguyen Ngoc

Reputation: 1555

declare cursor1 cursor for
select table1_index from table where (table1_date < dateadd(dd, -365, getdate())) 
and table1_index NOT IN (SELECT table1_index FROM audit_trail)

You need add condition and table1_index NOT IN (SELECT table1_index FROM audit_trail). I hope it will work for you.

Upvotes: 1

Related Questions