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 |
|
Logicalman
Starting Member
6 Posts |
 Posted - 2005-05-06 : 16:13:34
|
| Greetings,I have a rather large Stored Proc, that completes a number of varied tasks, including reading/writing table data and various calculations. For these purposes I use a number of #tables.The SProc works well and efficiently.During a recent Code Review, I was asked to include error trapping in the code.To this end I have included 'If Exists' statements for dropping the #tables at the end, by default.I now need to create the error catch to jump out of the code whenever an error occurs.I was considering using WHILE @@ERROR=0, with the END just prior to the drop table statements, or even declaring an error variable and setting that after each TSQL transaction, and testing for that. The former seems the easiest, the latter the hardest (due to the number of transactions carried out in this SProc), but it just has to be bullet-proof.Personally, I cannot see why it is required, but as the team say it is, then it is.I would appreciate any (non-sarcastic- :-) ) helpful comments on this matter prior to coding it.Thanks |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-05-06 : 17:11:35
|
| read up...http://www.sqlteam.com/item.asp?ItemID=2463 |
 |
|
|
Logicalman
Starting Member
6 Posts |
Posted - 2005-05-06 : 17:20:08
|
| ...nathans ... thanx, I'll take a look over the weekendTony |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-05-06 : 17:21:15
|
| Darn, I was all set to go until I got to your (non-sarcastic) qualifier...oh well.The best bullet-proofing is done on a case by case basis. In other words if you want to drop an object, first check for its existance. If you want to insert a record, first check if the new record will violate a constraint. If you want to edit a record that you expect to be there, check @@rowcount to make sure it updated something. etc... If an error occurs, checking @@Error will only work if the error severity level was low enough to continue the flow of execution. So @@Error doesn't hurt but it's not fool proof. For each scenario, you'll have to decide if you want to log an event and keep going, or raise an error or print statment and keep going, or if you want to "return" a specific return code, or if you want to "goto" a generic error handler block. Error handling really is vital for this type of processing and is worth the time to do right. BTW, unless you're concerned with tempdb growing too big and you can delete some #tables while the sp is still working then dropping them is a good idea. However, if you're just cleaning up after yourself at the end of the sp, sql will do that for you. Dropping is not necessary. Make sure you're up on raiserror, return codes, and control of flow commands.Be One with the OptimizerTG |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-05-06 : 17:23:50
|
| What transaction isloation are you using?I see so much useless error handling used because they are unaware of the IMPLICIT transaction modes and SQL Server's appalling (yes appalling - bring on 2005!) error handling. DavidMA front-end is something that tries to violate a back-end. |
|
|
|
Logicalman
Starting Member
6 Posts |
Posted - 2005-05-06 : 17:27:18
|
| TG .. thanx ... I have already implemented If Exists for the DROP TABLES at cleanup. I realize it's not (really) required, as SQL is supposed to clean up the TempDB after itself, but I just don't trust it, simply put, and so I go by 'best practice' and do my own garbage removal.I take the point on about a case-by-case basis, and, in conjunction with the article Nathans indicated, I will look to test for Fatals only prior to taking the act, and leave non-fatals to be caught solely by the @@ERROR test.Also, you are right, if I am going to this trouble, I should also have an error log table indicating what went wrong.Thanx again,Tony |
|
|
|
|
|
|
|
|
Best Error Trapping for SProc??