Getting check constraint error from sproc?

Hello everybody,

I was wondering if it was possible to get the name of the check constraint that caused an error (if it was the cause of an error) in a stored procedure as a way to handle errors.

For example, I have a stored procedure that adds a record to a table and I'd like to have a more user-friendly error message than just showing the error name, which still is useful.

Thank you,

Zach

re: Getting check constraint error from sproc?

Check out Try...Catch

Here are also some good reference.

-- CK

re: Getting check constraint error from sproc?

I checked out the TRY/CATCH but I'm stuck with SQL Server 2000 for this job, so I can't use this. Is there anyway to capture the name of a check constraint using the 2000 method (of checking @@ERROR) before the constraint actually stops the stored procedure (without doing a trigger of sorts?)

re: Getting check constraint error from sproc?

You could always do your error checking in the stored proc itself and return your own sensible error messages back to the front end.

re: Getting check constraint error from sproc?

Thanks Delerna--that seems like the only way. I'm kind of inexperienced when it comes to working in companies with SQL Server, but is it standard to do a lot of validating/error checking in stored procedures (instead of enforcing rules through table constraints and keys, etc.)

Zach

re: Getting check constraint error from sproc?

No, not instead of. Setting up table constraints,keys and relationships is very important. But as you mention in your post, left to its own devices, SQL server dosn't supply the friendliest of error messages back to a front end. So what I was refering to is more in "addition to" so that you as the developer can supply friendlier error messages (in situations where that is important) to your users.