Monday, 4 June 2012

Error handling

Error handling is a very important part of T-SQL development life cycle. Before SQL Server 2005 the error handling is quite difficult to maintain.
From SQL Server 2005 Microsoft provide some easier process to handle our T-SQL error properly. In this article we are discussing about the Error handling process of SQL Server.


The error information that SQL Server passes to the client consists of several components, and the client is responsible for the final interpretation of the message. These are the components that SQL Server passes to the client.


Message number 

Each error message has a number. We can find most of the message numbers in the table sysmessages in the master database. But there some special numbers like 0 and 50000 that do not appear there.


Severity level 

A number range from 0 to 25. This is that if the severity level is in the range 0-10, the message is informational or a warning, and not an error. Errors resulting from programming errors in our SQL code have a severity level in the range 11-16. Severity levels 17-25 indicate resource problems, hardware problems or internal problems in SQL Server, and if the severity is 20 or higher, the connection is terminated. For system messages we can find the severity level in master ..sysmessages.


State 

A number range from 0 to 127. The meaning of this item is specific to the error message, but Microsoft has not documented these values.


Procedure 

In which stored procedure, trigger or user-defined function the error occurred. Blank if the error occurred in a plain batch of SQL statements (including dynamic SQL).


Line 

The line number within the procedure/function/trigger/batch the error occurred. A line number of 0 indicates that the problem occurred when the procedure was invoked.


Message text

This is the actual text of the message that tells us what went wrong. We can find this text in master..sysmessages, or rather a template for it, with placeholders for names of databases, tables etc.

Error handling process in Microsoft SQL Server 2000


As our main concern is to demonstrate the error handling process at MS SQL Server 2005 and above, we are not going to depth of MS SQL Server 2000 error handling process.


@@error


Microsoft SQL Server sets the global variable @@error to 0, unless an error occurs, in which case @@error is set to the number of that error. @@error will hold the number of that message.
Now we are trying to generate error and display the error message by @@error global variable.


CREATE TABLE notnull(a int NOT NULL)

DECLARE @err int,
        @value int
       
INSERT notnull VALUES (@value)

SELECT @err = @@error

IF @err <> 0
   PRINT '@err is ' + ltrim(str(@err)) + '.'


Msg 515, Level 16, State 2, Line 5
Cannot insert the value NULL into column 'a', table 'MAHAGUN.dbo.notnull'; column does not allow nulls. INSERT fails.
The statement has been terminated.
@err is 515.


@@rowcount 
This is a global variable reports the number of affected rows in the most recently executed statement. Just like @@error we need to save it in a local variable if we want to use the value later, since @@rowcount is set after each statement. Since with SET we can only assign variable at a time, we must use the SELECT if you need to save both @@error and @@rowcount into local variables:
SELECT @err = @@error, @rowc = @@rowcount


@@trancount


This is a global variable which reflects the level of nested transactions. Each BEGIN TRANSACTION increases @@trancount by 1, and each COMMIT TRANSACTION decreases @@trancount by 1. Nothing is actually committed until @@trancount reaches 0. ROLLBACK TRANSACTION rolls back everything to the outermost BEGIN TRANSACTION (unless we have used the fairly exotic SAVE TRANSACTION), and forces @@trancount to 0, regards of the previous value.


Error handling process in Microsoft SQL Server 2005


The Microsoft SQL Server 2005 provides TRY… CATCH mechanism to handle with error. It is so effective and easy to use for T-SQL developer.


The syntax of the blogs is mentioned bellow.


BEGIN
     BEGIN TRY
         BEGIN TRANSACTION
         -- TSQL Starements
         COMMIT TRANSACTION
     END TRY
    
     BEGIN CATCH
         -- Error Handling Code
         ROLLBACK TRANSACTION
     END CATCH
END

Here in this block of BEGIN TRY and END TRY if any kind of error occurs it directly jump to the BEGIN CATCH and END CATCH block and handle the error.


The values that can be retrieved from the error are also much more detailed, then what you could get with previous versions of SQL Server.  Below is a list of the data that can be retrieved when an error occurs.
  • ERROR_NUMBER() - returns the number of the error.
  • ERROR_SEVERITY() - returns the severity.
  • ERROR_STATE() - returns the error state number.
  • ERROR_PROCEDURE() - returns the name of the stored procedure or trigger where the error occurred.
  • ERROR_LINE() - returns the line number inside the routine that caused the error.
  • ERROR_MESSAGE() - returns the complete text of the error message.
A sample code of calling stored procedure using error handling technique is mentioned bellow.


BEGIN TRY
     BEGIN TRY
          EXECUTE up_MyProc
     END TRY

     BEGIN CATCH
          SELECT
               ERROR_NUMBER() AS ErrorNumber,
               ERROR_SEVERITY() AS ErrorSeverity;
     END CATCH;

     EXECUTE up_MyProc
END TRY
BEGIN CATCH
     SELECT
          ERROR_NUMBER() AS ErrorNumber,
          ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;


RAISERROR() statement


Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically.


RAISERROR can be used to:

1.    Assign a specific error number, severity and state.
 
2.    Record the error in Windows Application Log.
 
3.    Return messages that contain variable text.

Example:


DECLARE @DBID INT;
SET @DBID = DB_ID();

DECLARE @DBNAME NVARCHAR(128);
SET @DBNAME = DB_NAME();

RAISERROR
    (N'The current database ID is:%d, the database name is: %s.',
    10, -- Severity.
    1, -- State.
    @DBID, -- First substitution argument.
    @DBNAME); -- Second substitution argument.
GO


Error handling process in Microsoft SQL Server 2012


In Microsoft SQL server 2012 code named denali in traduced HROW statement.
The statement before the THROW statement must be followed by the semicolon (;) statement terminator.


If a TRY…CATCH construct is not available, the session is ended. The line number and procedure where the exception is raised are set. The severity is set to 16.
If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised. Any error that occurs in a THROW statement causes the statement batch to be ended.

RAISERROR statement
THROW statement
If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.
The error_number parameter does not have to be defined in sys.messages.
The msg_str parameter can contain printf formatting styles.
The message parameter does not accept printf style formatting.
The severity parameter specifies the severity of the exception.
There is no severity parameter. The exception severity is always set to 16.


Example:


-- IN SQL Server 2005
 BEGIN TRY
         DECLARE @VarToTest INT
         SET @VarToTest = 'C'
 END TRY
 BEGIN CATCH
         DECLARE  @ErrorMessage NVARCHAR(4000),
                @ERRORSEVERITY INT

         SET @ERRORMESSAGE = ERROR_MESSAGE()
       SET @ERRORSEVERITY = ERROR_SEVERITY()

       RAISERROR (@ErrorMessage, @ErrorSeverity, 1 )
 END CATCH


-- IN SQL Server 2012

 BEGIN TRY
      DECLARE @VarToTest INT
     
      SET @VarToTest = 'C'
 END TRY
 BEGIN CATCH
      THROW
 END CATCH

Hope you like it.





Posted by: MR. JOYDEEP DAS

3 comments:

  1. Thanks... realy interesting.... In next article can you give more details on error handling in 2012 with example please.

    ReplyDelete
    Replies
    1. Thanks "Vikash"
      I have a plan for that ... but not in the next article.

      Delete
  2. He may mean like "throw what?"

    ReplyDelete