How to prevent a stored proc error from throwing exception in SqlClient?

Question

Say i have a stored proc that inserts some data into a table. If data is duplicate it will cause a sql server error (because i have unique index on some columns) and this error causes an exception in SqlClient. What can i do inside the stored proc to handle the error so no exception is being generated in my data access code?

Answer 1

You can use TRY CATCH within your stored procedures.

This will allow you to do error handling withing SQL Server and not expose the errors to calling code.

Of course, if your CATCH statements cause exceptions/errors themselves they will be exposed to the client.

However, you need to ask yourself if you really do not want to know such errors - they are indications of issues in your database/codebase.

Answer 2

Use MERGE instead of INSERT

Answer 3

When you create your unique index, you can tell it to silently ignore any duplicates:

CREATE UNIQUE INDEX UIX_YourTable
  ON (SomeColumn) WITH IGNORE_DUP_KEY = ON

Whether that's a good idea or not is up to you to decide. If you have such an unique index, any duplicates that you try to insert will neither cause an exception, nor will they be inserted - they just silently fail.

Pro: no exception

Con: really quite hard to debug cases where you scratch your head wondering why on earth a given row hasn't been inserted into the table, and you had no exception and no error and nothing.... (just happened to me today as a matter of fact)

Answer 4

I hate to say it, but the ideal answer here is simply not to have the sproc cause errors, but rather to validate the inserts. Or better, for the code invoking the sproc to only ask it to process valid data. Papering over the cracks is only going to make things uglier - it raises an exception (above a certain error-level) quite deliberately.