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 |
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
 Posted - 2011-04-20 : 11:11:55
|
I have a bunch of stored procedures that do simple inserts from import tables to staging tables. They have at the end of each one a return statement for the error if there is one: IF @@ERROR <> 0 BEGIN RETURN -1 END RETURN 0 I want to write a procedure that checks all these and logs it somehow so it can be known that one failed. They are just EXEC statements like this:EXEC uspTest1-- Check for errors in here--EXEC uspTest2-- Check for errors in here--EXEC uspTest3-- Check for errors in here---- etc. What is a common, fairly easy way to do this?Duane |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-04-20 : 11:25:28
|
Depending on what version of SQL you are using you should probably be using a TRY..CATCH block. But, if you need to do some special processing you can do something like:DECLARE @ReturnStatus INT;EXEC @ReturnStatus = uspTest1IF (@ReturnStatus == -1)BEGIN ...END I'd also suggest returning the actual @@ErrorCode instead of trapping for an error and making up your own return code (just a suggestion). |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-20 : 11:27:59
|
| On SQL 2005 and above, I would prefer to use TRY/CATCH. http://msdn.microsoft.com/en-us/library/ms175976.aspxDepending on your requirements, you can wrap the call to each stored proc in its on try/catch block, or the entire set of calls in a single try/catch block.Try/catch also provides you a lot more information on the errors - described on the link above. You can also make use of XACT_STATE to determine if there are committable transactions and if there are, you can decide to commit or rollback. |
|
|
|
|
|
|
Erro handling in stored procedure