Feeds:
Posts
Comments

Archive for the ‘Control-of-Flow Language’ Category

In my earlier articles, I wrote about THROW statement and its enhanced features that give us more control over the debugging.
I also implemented it with some clients and found it much more effective than raiserror. Given below are the guidelines that must be followed to avoid any error.

  1. We usually do not use (;) semicolon terminator in SQL Server, however it is recommended to use.  But make sure to use it prior to any statement before THROW statement. Given below is the example.
  2. BEGIN TRY
         DECLARE @VALUE INT
         SET @VALUE = 1 / 0
    END TRY
    BEGIN CATCH
         PRINT 'ERROR';--Do not forget to put this semicolon
                       --prior to THROW Statement
         THROW
    END CATCH
    

    If you fail to use the semicolon terminator prior to THROW statement, SQL Server will generate given below error message.
    Msg 102, Level 15, State 1, Line 7
    Incorrect syntax near ‘THROW’

  3. Sometimes we pass THROW statement without parameter, so make sure that THROW Statement is inside CATCH block as shown in the above example. If you fail to do it, SQL Server will generate given below error message.Msg 10704, Level 15, State 1, Line 9
    To rethrow an error, a THROW statement must be used inside a CATCH block. Insert the THROW statement inside a CATCH block, or add error parameters to the THROW statement.
  4. We usually pass the customized message_id in the THROW statement, make sure you use message_id between 50000 and 2147483647. Given below is the example.
    EXEC sys.sp_addmessage @msgnum = 80000 ,@severity = 16
    ,@msgtext = N'This is a test message.'
    ,@lang = 'us_english';
    GO
    
    THROW 80000 , N'This is a test message', 1;
    

    if you fail to do it, SQL Server will generate given below error message.
    Msg 35100, Level 16, State 10, Line 9
    Error number 49999 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647.

  5. THROW does not allow the substitution parameter, so if you need to use it, always use with the FORMATMESSAGE function. Given below is the example.
     EXEC sys.sp_addmessage
     @msgnum = 90000
    ,@severity = 16
    ,@msgtext = N'This is a %s test message'
    ,@lang = 'us_english';
    GO
    
    DECLARE @msg NVARCHAR(50) =
    FORMATMESSAGE(90000, N'raresql');
    
    THROW 90000, @msg, 1;
    
  6. Cleaning :

    USE master;
    GO
    EXEC sp_dropmessage 80000;
    GO
    EXEC sp_dropmessage 90000;
    GO
    

    Reference : MSDN

    Read Full Post »

A few months ago, I wrote an article about THROW a new technique to raise an exception and transfer execution to a CATCH block shipped with SQL Server 2012. I also compared throw with Raiserror in that article partially. Later on I found a lot of differences and found that THROW is much better than RAISERROR. Let me create a sample error message to demonstrate the difference.

EXEC sys.sp_addmessage
     @msgnum   = 80000
    ,@severity = 10
    ,@msgtext  = N'This is a test message.'
    ,@lang = 'us_english'; 
GO

Let me explain the differences:

No

RAISERROR

THROW

1


If you pass any message_id to RAISERROR, the message_id must be available in sys.messages.

Example :

RAISERROR (51000, -- Message id.
 10, -- Severity,
 1, -- State,
 N'This is a test message');

–OUTPUT
Msg 18054, Level 16, State 1, Line 1
Error 51000, severity 10, state 1 was raised, but no message
with that error number was found in sys.messages.
If error is larger than 50000, make sure the user-defined
message is added using sp_addmessage.


If you pass any message_id to THROW, the message_id is not necessary to be available in sys.messages.
Example :

THROW 51000
, 'This is a test message'
, 1
;

–OUTPUT
Msg 51000, Level 16, State 1, Line 1
This is a test message

2


If you pass any message_id to RAISERROR, the message_id range must be between 13000 and 2147483647 and it cannot be 50000.
Example :

RAISERROR (49913, -- Message id.
           10, -- Severity,
           1, -- State,
           N'This is a test message');

–OUTPUT
The server could not load DCOM. Software Usage Metrics cannot be started without DCOM.


If you pass any message_id to THROW, the message_id range must be between 50000 and 2147483647.
Example :

THROW 49913
, N'This is a test message'
, 1
;

–OUTPUT
Msg 35100, Level 16
State 10, Line 1
Error number 49913 in
the THROW statement
is outside the valid range.
Specify an error number
in the valid range of
50000 to 2147483647.

3


If you pass any existing message_id to RAISERROR, it will act upon the same Severity level.

Example :

RAISERROR (80000, -- Message id.
           10, -- Severity,
           1, -- State,
           N'This is a test message'); 

–OUTPUT
This is a test message.


If you pass any message_id to THROW, whatever the Severity level is, it will always show Severity level 16.
Example :

THROW 80000
, N'This is a test message'
, 1
;

–OUTPUT
Msg 80000, Level 16
State 16, Line 1
This is a test message

4


If you use RAISERROR to trap the bug, it will raise the error but with the incorrect error number.
Example :

BEGIN TRY 
    DECLARE @VALUE INT
    SET @VALUE = 1 / 0 
END TRY 
BEGIN CATCH 

DECLARE @ErrorMessage NVARCHAR(4000); 
DECLARE @ErrorSeverity INT; 
DECLARE @ErrorState INT; 

SELECT
@ErrorMessage = ERROR_MESSAGE(), 
@ErrorSeverity = ERROR_SEVERITY(), 
@ErrorState = ERROR_STATE(); 
  
RAISERROR (
@ErrorMessage, -- Message. 
@ErrorSeverity, -- Severity. 
@ErrorState -- State. 
          ); 
END CATCH; 

–OUTPUT
Msg 50000, Level 16
State 1, Line 15
Divide by zero error encountered.


If you try to use THROW to raise the error, it will give you the exact error number.
Example :

BEGIN TRY 
    DECLARE @VALUE INT
    SET @VALUE = 1 / 0 
END TRY 

BEGIN CATCH
  THROW;
  END 
CATCH











;

–OUTPUT
Msg 8134, Level 16
State 16, Line 4
Divide by zero error encountered.

5


RAISERROR can contain printf formatting styles
Example :

RAISERROR (
N'@@@%*.*s@@@',
10,
1,
0,
30,
N'This is a test message 
For SQL Server 2012');
GO

–OUTPUT
@@@This is a test message For SQL@@@


THROW cannot contain printf formatting styles.
Example :

THROW 80000
,N'@@@%*.*s
@@@This is a test message'
, 1




;

–OUTPUT
Msg 80000, Level 16
State 1, Line 1

Conclusion :
As per the above differences THROW function leads over RAISERROR and in my opinion also THROW is better to raise an exception and transfer execution to a CATCH block but again it depends upon the requirements.

Read Full Post »

Whenever we write a code, we need to do the error handling in it and as much as the handling is strong, it is as simple to trap the bug in the system. SQL Server 2012 provided a new and a flexible way of doing the error handling with the additional features to make it effective and efficient.

First, I will discuss how we were doing error handling in 2005/2008 and what was missing in RAISERROR and why we need this new functionality of THROW.

 -- Error HANDLING in SQL Server 2005/2008
BEGIN TRY
	DECLARE @VALUE INT
	SET @VALUE = 1 / 0
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    Print ERROR_NUMBER() --Actuall Error Number
    Print ERROR_LINE()   --Actuall ErrorLine

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;
--OUTPUT

8134  — Actual Error Number
4     — Actual Error Line Number
Msg 50000, Level 16, State 1, Line 19
Divide by zero error encountered.

If you look at the error detail, you get the feeling that RAISERROR detail is unable to handle the error properly. It displays the information message like  Divide by zero error encountered which is correct but the rest of the info like error number 50000 & line number 19 is incorrect because these are not the actual error & line numbers respectively.

Now, lets try the same in SQL SERVER 2012 with THROW and notice the accuracy.

-- Error HANDLING in SQL Server 2012
BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 1 / 0
END TRY
BEGIN CATCH
        THROW
END CATCH;

--OUTPUT

Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.

So, what is it that I did in SQL Server 2012 ? I just replaced all error handling with one keyword “THROW” and it returns more accurate result Like error number 8134 and line number to the caller, than the RAISERROR. Also, it reduces the number of codes.

Read Full Post »