Wednesday, October 1, 2014

0 T-SQL Error Handling

This article demo TRY/CATCH, THROW(),RAISERROR() and ERROR functions. 

THROW

CREATE DATABASE TSQLDB;
USE TSQLDB;
create table dbo.testThrow (
    ID int PRIMARY KEY
);
-- Use CATCH 
BEGIN TRY    
    INSERT dbo.testThrow(ID) VALUES(1); 
-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.   
    INSERT dbo.testThrow(ID) VALUES(1); 
END TRY 
BEGIN CATCH     
    PRINT 'Catch Block.'; 
END CATCH; 
 
-- Throw
BEGIN TRY    
    INSERT dbo.testThrow(ID) VALUES(1); 
-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.   
    INSERT dbo.testThrow(ID) VALUES(1); 
END TRY 
BEGIN CATCH     
    PRINT 'Catch Block.'; 
    THROW;
END CATCH; 

The first statement will catch the error but it wont display the actually error message because it has been catch.

image_thumb4

After we add the THROW, it will also display the actual error message

image_thumb5

Msg 2627, Level 14, State 1, Line 18
Violation of PRIMARY KEY constraint 'PK__testThro__3214EC2789EB1AE6'. Cannot insert duplicate key in object 'dbo.testThrow'. The duplicate key value is (1).

THROW Custom error

We can also use throw to throw the custom error. If we use the throw the custom error, the severity level would always be 16.

BEGIN TRY    
    INSERT dbo.testThrow(ID) VALUES(1); 
-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.   
    INSERT dbo.testThrow(ID) VALUES(1); 
END TRY 
BEGIN CATCH     
    PRINT 'Catch Block.'; 
    -- THROW;
    THROW 50000,'I throw you TEST',1;
END CATCH; 

image

RAISEERROR

RAISEERROR is used to raise the custom error message or use sp_addmessage into the sys,messages so it can be reused again.

EXEC sp_addmessage @msgnum = 50005,
              @severity = 10,
              @msgtext = N'This is from sys.message';
GO
BEGIN TRY    
    INSERT dbo.testRAISEERROR(ID) VALUES(1); 
-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.   
    INSERT dbo.testRAISEERROR(ID) VALUES(1); 
END TRY 
BEGIN CATCH     
     RAISERROR (N'This is message Violation of PRIMARY KEY constraint.', -- Message text.
               17,-- Severity.
               1 -- State
               );
    RAISERROR (50005, -- Message id.
           14, -- Severity,
           1 -- State
           ); 
END CATCH; 
EXEC sp_dropmessage 50005;

image

ERROR FUNCTONS

BEGIN TRY    
    INSERT dbo.testThrow(ID) VALUES(1); 
-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.   
    INSERT dbo.testThrow(ID) VALUES(1); 
END TRY 
BEGIN CATCH     
    PRINT 'Catch Block.' ;
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH; 

image

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates