Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-07 : 09:05:37
|
| Daryl writes "Have you any articles, references or opinions on what the best practice is to: display human readable error messages.For Example, lets say we want the field UserName to be unique. Placing the Unique Constraint on the field ensures this, but when someone is trying to add a user name that is already in use the SQL Server would spit out something like: > Violation of UNIQUE KEY Constraint > 'IX_tblUser_Company_UserName'. Cannot insert duplicate > key in object 'tblUser'. The statement has been terminated.To display a nice error message like: "That User Name has already been taken. Please choose another."1. We could test for the Username before the update in a stored proc, but this would add an extra query while the DB engine is just going to do this anyway.- OR -2. We could translate the Error Number (2627 for UNIQUE KEY Violation) and test for the index name... like this: Function TranslateSQLErrors(ex As SqlException) As String Select Case ex.Number Case 2627: If ex.Message.IndexOf("IX_tblUs...Name") >= 0 Then Return "That User Name has already been taken......." End If Case Else: Return ex.Message End Select End Function" |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-03-07 : 10:13:27
|
| Check for RaiseError in BOL ,it might help you.-------------------------------------------------------------- |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-07 : 10:17:23
|
| I think that you're really asking whether it's better to have all error handling on the database, or have it in the application. There's no "correct" or "best" answer because each method has its pros and cons, and your situation will dictate which is best for you.If you have the database maintain all data-related error handling, you have a consistent, reliable and maintainable process. Regardless of how people access the database (custom VB app, query analyzer, ASP/ADO web app) they'll get the same error messages and handling. You can always trap errors (using the @@ERROR variable) or, even better, test for possible error conditions and avoid them, then submit your own custom error/message using RAISERROR.Server-side error handling comes at the expense of flexibility though, since some applications may need further or customized handling. Doing all the error handling on the application or client side allows you to tailor error handling as much as you like. The downside is that this needs to be done for each type of app, and it's harder to maintain consistency. It's also NOT a substitute for proper database integrity (constraints, primary and foreign keys) although you or someone else might tempt you to think otherwise. Having only one possible avenue for data access (a custom app) lessens these issues but doesn't completely eliminate them.My personal preference is to keep error handling as close to the active process as possible, so if I'm pulling data from SQL Server, the stored procedure would handle the errors. If my application needs to import a file, it (not the database) would check for the file's existence before proceeding. Either method still allows for customized error messages. I also do as much as possible to prevent errors from happening in the first place (testing for duplicate rows BEFORE inserting them, for example).Take a look at these articles for more ideas on error handling in SQL Server:http://www.sqlteam.com/SearchResults.asp?SearchTerms=errorHTH |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-03-07 : 10:35:22
|
| I would say that errors are basically that i.e. errors and should not be used for controlling normal processing.In the case you give I would first check for the existence of the name - if it exists then give back a friendly message, probably by use of a return code but it's up to you but I wouldn't raise an error as this isn't one.You could also lock the table to make sure that no one inserts the name before it is used but that may slow down the processing so you could use the error processing to handle that.On any errors I will close the connection and open a new one - log the error and investigate it at some point. You don't want a support department spending all their time finding out what are errors and what are thingsd that the developers have coded in.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
DManB
Starting Member
9 Posts |
Posted - 2002-03-07 : 12:19:52
|
| In the case where you are avoiding the errors by testing before performing the operation, don't you find that you are just redoing what proper constraints and referential integrity are doing? Why perform extra table lookups for duplicate rows when the DB engine already does that??? In many cases, the only problem is that the error messages are to cryptic for users. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-07 : 12:58:08
|
| It's not as apparent until you look at what happens if there are many duplicates. A constraint-violating error will only tell you about one dupe. You'd have to remove each dupe as a group (in which case you need to identify them all ahead of time anyway!) or remove each one at a time as you try to INSERT them. Would a user prefer to be told in advance that "There are many duplicates in this file, here's a list of them:" vs. hundreds of "This row X is a duplicate. Please remove it."In terms of handling this, instead of even throwing an error, you can write your procedure to insert ONLY those rows that are not dupes:INSERT INTO Table1 SELECT * FROM Table2 B WHERE NOT EXISTS (SELECT * FROM Table1 A WHERE A.Col1=B.Col1)This example assumes col1 is common between both tables and unique in Table1 (no dupes allowed). This kind of processing will never generate an error at all, unless you specifically want to tell the user that there are duplicates. If the user is expecting that the system will automatically handle dupe rows, this is an ideal solution.I have a smart-ass philosophy that "Error handling is for code that has errors in it", but the idea behind that statement is that you're NOT treating duplicate handling as an error, but as part of the larger process of inserting rows of data. Of course it's not realistic to eliminate all errors. But any effort you make to avoid system errors is NOT a waste of time...consider it preventive maintenance, "an ounce of prevention is worth a pound of cure", etc. And THAT I would consider a "best practice".Edited by - robvolk on 03/07/2002 13:02:46 |
|
|
|
|
|
|
|
|
Best Practices : Error Testing vs. Error Message Translating