How can I handle an error

Is it possible to catch and error and then keep the process going in a stored procedure?

So if an update encounters a primary key violation on a row, is it possible to skip that row and keep the process going?

You can use a try block in a stored procedure

http://msdn2.microsoft.com/en-us/library/ms175976.aspx

|||

Ken,

I have looked at the try block, but I'm not sure how to set it up. Here is what I'm doing, can you giving me any help on adding the try block so it will keep processing after it catches a primary key violation on a row?

 @.KeepClientIDint, @.RemoveClientIDintASBEGIN UPDATE tbOrgCodesSET tblOrgCodes.Client_ID = @.KeepClientIDWHERE tblOrgCodes.Client_ID = @.RemoveClientIDEND

|||

I added the try/catch and it stopped the error, but it did not continue the process. What do I need to do to get it to continue?

@.KeepClientIDint, @.RemoveClientIDintASBEGINBEGIN TRYUPDATE tbOrgCodesSET tblOrgCodes.Client_ID = @.KeepClientIDWHERE tblOrgCodes.Client_ID = @.RemoveClientIDEND TRYBEGIN CATCH--?END CATCHEND

|||

Hi Jack,

We can catch the exception that is thrown within this single update. If the update affects serveral rows, we cannot only ignore one row and proceed with other rows.

However, if you're calling the DataAdapter.Update method to update rows. When one row throws exception, and you need to go on with other rows, you can set DataAdapter.ContinueUpdateOnError to true, so that it will continue with the next row when error generates.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!