Error handling in cursor

I have cursor that loops through some records and calls a stored procedure. Sometimes the stored procedure raises an error. When an error is raised the cursor stops looping.

I would like the cursor to loop to the next record and continue executing the stored procedure. How do I implement this functionality? I suspect it has to do with using @.@.Error but I have limited experience with it. Thanks in advance.

Code Snippet

DECLARE PKIDs_Cursor CURSOR FOR

SELECT Id as PKID FROM Table1

OPEN PKIDs_Cursor;

DECLARE @.PKs VARCHAR(20)

FETCH NEXT FROM PKIDs_Cursor INTO @.PKs;

WHILE @.@.FETCH_STATUS = 0

BEGIN

EXEC dbo.spProcessInfo @.PKs, NULL;

FETCH NEXT FROM PKIDs_Cursor INTO @.PKs;

END;

CLOSE PKIDs_Cursor;

DEALLOCATE PKIDs_Cursor;

By default, SQL Server follows “ON ERROR RESUME NEXT”, that means when error occurs (if it is not critical error) it allows continuing to execute the next statement. Considering this statement your loop won’t break. You need not to use the @.@.ERROR here. If you want to force to discontinue the loop then only you need this variable.

Note:

In SQL Server 2000 you can’t suppers the error message thrown by the server. But the execution will continue.

If you use SQL Server 2005 then you can use the TRY..CATCH to suppress the error messages.

If you provide the error message it will be more helpful.

|||

Thank you for the reply Manivannan. I am using SQL Server 2000. The error message is a custom message raised by the stored procedure:

-2 Warning: Mapping failed - No actions taken.

“ON ERROR RESUME NEXT” behavior does not appear to be in effect. Any ideas?|||

What Severity Level you used on your Raiserror? Severity Level might affect the work-flow of your batch.