I have this sql procedure:
create procedure DELETE as
DECLARE VARIABLES
begin TRY
DECLARE CURSOR FOR SELECT ANYTHING
OPEN CURUPD
FETCH NEXT FROM CURUPD INTO VARIABLES
WHILE @@FETCH_STATUS = 0 BEGIN
UPDATE TABLE BASED SON VARIABLES
FETCH NEXT FROM CURUPD INTO VARIABLES
END
CLOSE CUR
DEALLOCATE CUR
end TRY
begin catch
DO NOT CARE ABOUT THE ERROR BUT CONTINUE UPDATE ON NEXT RECORD
end catch;
Is this possible? Robert :-)
You would have to put the TRY/CATCH inside of the WHILE loop. The way you have it, you can't continue the WHILE loop, because it's already finished.
Try this code:
create procedure DELETE as
DECLARE VARIABLES
begin TRY
DECLARE CURSOR FOR SELECT ANYTHING
OPEN CURUPD
FETCH NEXT FROM CURUPD INTO VARIABLES
WHILE @@FETCH_STATUS = 0 BEGIN
UPDATE TABLE BASED SON VARIABLES
FETCH NEXT FROM CURUPD INTO VARIABLES
END
CLOSE CUR
DEALLOCATE CUR
end TRY
begin catch
FETCH NEXT FROM CURUPD INTO VARIABLES
continue
end catch;
some errors are not trappable and will abort the batch even with try and catch you could check XACT_STATE() and then determine what to do
Why do you need a cursor can't you do a SET based update? Also your try is outside of the loop but you will still have the same problem, try updating an int colum,n with 'A'....BOOM!
