|Business Application Development with: SQL Server, C#, VB, VB.Net, ASP, ASP.Net, and XML|
|News Links Schedule Site Map Contact|
Tips and Tricks for: SQL Server
The Problem: Does SQL Server have anything like VB's On Error GoTo?
To those familiar with Visual Basic, VB provides the option to use the ON
ERROR GOTO ... clause to catch practically any error and handle it in one's
Solution: Errors must be checked after every SQL statement of interest.
In short: No! SQL Server does not have anything like Visual Basic's "On Error GoTo" construct. It acts as if there was always an "On Error Resume Next" statement in effect.
For effective error handling in a SQL Server stored procedure you must test the value of @@ERROR after EVERY SQL statement that might produce an error. Yes, that's a lot of testing. A well written stored procedure will often be 1/2 or more error handling code.
There's another important consideration in designing error handling: @@ERROR is reset after each and every SQL statement, @@ROWCOUNT is also similarly reset. Therefore, you must capture both of these immediatly after each SQL statement that might produce an error.
I start by declaring two variables to hold the values of @@ERROR and @@ROWCOUNT while the error handling code is working with them. They're declared at the top of every stored procedure and I've added them to my stored procedure template.
DECLARE @myERROR int -- Local @@ERROR
Next let's say that we had a simple INSERT statement
INSERT INTO Authors (au_id, au_fname, au_lname, contract)
Immediately after that statement, you should have the following statement:
SELECT @myERROR = @@ERROR
These two assignements must be in the same statement. If you were to split them into two statements, for instance:
SET @myERROR = @@ERROR
then @myRowCOUNT would always be zero. That's because it's reflecting the number of rows effected by the SET @myERROR = @@ERROR statement that preceeds it.
Puling it all together and adding a little error handling you'd get:
-- These two should always be on when
created SPs and UDFs
CREATE PROCEDURE usp_Example_ErrorHandler /* Example stored procedure to illustrate error handling * Example: DECLARE @RC int EXEC @RC = usp_Example_ErrorHandler PRINT 'Return value = ' + CONVERT(varchar(10), @RC) ****************************************************************/ AS DECLARE @myERROR int -- Local @@ERROR , @myRowCount int -- Local @@ROWCOUNT SET NOCOUNT ON BEGIN TRAN INSERT INTO Authors (au_id, au_fname, au_lname, contract) VALUES ('222-22-2222' , 'Andrew' , 'Novick' , 1 ) SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT IF @myERROR != 0 GOTO HANDLE_ERROR INSERT INTO titles(title_id, title, type, price,notes, pubdate) VALUES('WW0790' , 'Transact-SQL User-Defined Functions' , 'popular_comp', 49.95 , 'Great book.', '2003-11-04') SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT IF @myERROR != 0 GOTO HANDLE_ERROR INSERT INTO titleauthor (au_id, title_id) VALUES('222-22-2222', 'WW0790') SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT IF @myERROR != 0 GOTO HANDLE_ERROR COMMIT TRAN -- No Errors, so go ahead RETURN 0 HANDLE_ERROR: ROLLBACK TRAN RETURN @myERROR
Now, let's execute the procedure:
DECLARE @RC int
To generate an error, run it again:
DECLARE @RC int
Notice that the error message has been returned, even though the code proceeded to capture and return the error value. This allows client code, such as ADO.Net and ADO to capture the message and return it to their caller.
There are other styles of error handling. Your choice depends on how you want your stored procedure to interact with it's callers. In this case I've used the proc's return value but still returned the error. That means that the client must check the return after every call to this procedure. Looks like there might be enough material to turn this into an article.