Good old fashion error handling……….This is something that is often skipped over when we code because we don’t stop and take the time out to plan it out. I’ve put together a presentation on this topic and I’ll be submitting it to the next SQL Saturday that I attend, but would like to share the main points here. Obviously, there is a lot I left out here, don’t want to spoil the presentation!
· Why do we need error handling?
One of the first things that comes to mind is that the calling process needs to be informed if a stored procedure/script has failed or was successful. It is also used to control the processing of transactions whether they are implicit or explicit. The user needs to be alerted if there is a problem too. Error handling allows us to be able to code for unexpected results and helps to provide data consistency.
- · @@ERROR Global Variable
@@ERROR is known as a system variable to SQL Server. It is read-only and contains the value of the error number that was generated in the previous transact SQL statement that just processed. Every statement is evaluated to determine whether or not it was successful. So for example:
USE master –@@ERROR contains 0
SELECT * FROM sysobjects –@@ERROR contains 0
USE model –@@ERROR contains 0
SELECT * FROM sysdatabases –@@ERROR contains 208
IF EXISTS (SELECT * FROM sysservers) –@@ERROR contains 208
SELECT 1=1 –@@ERROR contains 102
IF @@ERROR > 0 –@@ERROR contains 0
GOTO somewhere –@@ERROR contains 0
somewhere: –@@ERROR contains 0
· RETURN Statement
The RETURN statement when executed returns control to the call process unconditionally. It returns an integer value. A warning will be issued it the value is NULL.
· User-Defined Error Messages
The user-defined error messages are saved in master in sys.messages table. They can be used across databases. When a user-defined error message is called if can write to the Windows application log. The message can be informational or error related, depends on severity level. Severity level 20 thru 25 are fatal errors – processing has stopped.
· RAISERROR Statement
The RAISERROR statement is used to raise user-defined error messages. It can be used to raise dynamically built messages. The message is returned as a server message. It can also raise messages to a Catch block and insert them into the error and application log. Here is an example of a RAISERROR statement.
DECLARE @oldname varchar(15),
@newname varchar(15)
SET @oldname = 'Cato'
SET @newname = 'Tessereau'
RAISERROR (50001, –user-defined error message number
10, –severity level
1, –error state
@oldname,
@newname) WITH LOG;
· TRY/CATCH Blocks
These consist of two parts a TRY block and a CATCH block. Logic control is passed to the CATCH block from the TRY block when an error is encountered. They can be nested, but be careful when doing this because the logic can get very confusing the deeper you nest. Severity 20 or higher not handled by TRY/CATCH because connection is closed. Severity 10 or less not handled, these are informational only. Here is an example of a simple TRY/CATCH block.
CREATE PROC usp_myproc
AS
–just a silly little proc
SELECT * FROM <table that does not exist>;
GO
BEGIN TRY
EXEC usp_myproc
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrNum,
ERROR_MESSAGE() as ErrMsg;
END CATCH
GO
· XACT_ABORT statement
OFF is the default setting. Compile errors are not affected by XACT_ABORT.
When set to ON
· Processing stops when an error is encountered
· When run-time error is raised, entire transaction is rolled back automatically, no need for ROLLBACK statement.
· When combined with a TRY/CATCH block you get unexpected behavior
o SQL Server treats statement level errors as batch level errors
