Is there a way to trap an error and continue processing within cursor

[sonname]

I have a cursor and I was wondering if there is a way to trap an error, ignore the error and continue processing within the cursor. Almost like a resume next that you find in VB.

 

[micha123]

SQL 2000 or 2005?

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[sonname]

It is SQL Server 2000

 

[Catadmin]

You can use @@Error within your T-SQL Code. It's been a while since I've coded with it, so I can't give you an exact suggestion, but check it out in Books Online.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[micha123]

Well...

It depends what kind of "errors" you are expecting to get...
There are some errors that you cannot catch.
Try the following and let me know if it helped:

declare @Dummy tinyint

Declare cursor...

open cursor...

fetch ...


while @@fetch_status = 0
begin
      <Commands on cursor loop...>
      -- add the following after commands 
      -- that may fail:
      -- if @@error <> 0
      --       set @Dummy = 1

      fetch...

end

If the fetch fails - you cannot do anything.
I also suggest to break this after X numbers of errors...


"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.