SQL Server Performance Forum – Threads Archive

Removing ADO Errors from SQL

I have a stored procedure in which, among other things, a constraint error might be raised. This error will get added to the ADO Errors collection (and hence cause the catch(…) in my try/catch on the ASP page which invoked the SP to get engaged). But, the stored proc can handle the constraint error and still complete successfully. Is there a way that my stored proc can signify that the error was handled and prevent that constraint error from even making it into the ADO Errors collection? Thanks,
Rose

---

It can be tricky because depending on the severity level of the error you might not always be able to check for the error in your stored procedure. The execution will sometimes be aborted and the error thrown back to the caller, i.e. ADO. I’m not sure where on the severity scale a constraint error is but you can simply try and catch the error in the stored procedure by checking @@ERROR after the statment that can cause the constraint error. Or is this what you are doing already and the error still propagates up to the ADO errors collection? Or what do you mean by "the stored proc can handle the constraint error"? /Argyle

---

The severity level for a constraint error is not high enough to cause the stored proc to abort. And yes, I am checking @@ERROR afterward. What I mean by being able to handle the error is that the stored proc can detect the error and that it does not prevent what the proc is trying to do overall from being accomplished – that is, it is "safe" for the proc to continue. And yes, even with me checking @@ERROR, the constraint error is still being added to the ADO Errors collection – and it is that which I would like to prevent. I’d like to keep the frontend unaware of this particular error. Thanks,
Rose

---

I know of no way to do what you want. My advice would be for your client app to check the severity of errors in the error collection, and ignore them unless their severity is 16 or greater. And then whenever you RAISERROR manually, make sure you always set severity to >= 16

---

I might be missing something… but isn’t there a way for you to check before executing the statement that might cause this constraint error? I assume you are inserting or updating data. can’t you add a where statement to ensure this constraint error will be occure? Bambola.

---

All errors with severity 11 or higher (and errors with a severity of 10 or lower are considered informational messages) will _always_ be raised to the client. No way around it. Handling the error in the stored procedure itself will not prevent it from being raised to the client. As bambola suggest, if this is an issue, check the data first before you insert it. The only valid reason to check it in the stored procedure (as opposed to the client) is if it is a Unique constraint. If it is a check or a foreign key you can validate the input on you webpage by limiting the input possibilities using a drop down list or so. That does not mean that you should not have the constraints in place in your database, just that you want to prevent a roundtrip to the server to check something you can check on your webpage as well.

---

]]>