2019-07-03

TSQL - Try and catch and raise error


-- Declarations
declare @error_message nvarchar(2000);
set @error_message = null;
declare @error_severity nvarchar(2000);
set @error_severity = null;
declare @error_state nvarchar(2000);
set @error_state = null;
declare @error_number int;
set @error_number = null;


-- Try
BEGIN TRY 
       -- Do something here
END TRY 


-- Catch
BEGIN CATCH 

       -- Available error variables
       /*SELECT
              ERROR_NUMBER() 
              , ERROR_SEVERITY() 
              , ERROR_STATE() 
              , ERROR_PROCEDURE() 
              , ERROR_LINE() 
              , ERROR_MESSAGE() 
       ; */

       -- Error number
       set @error_number = ERROR_NUMBER();                   

       -- Capture the error message, severity and state
       set @error_message = ERROR_MESSAGE();
       set @error_severity = ERROR_SEVERITY();
       set @error_state = ERROR_STATE();

       -- Debug
       print 'ERROR CAUGHT -> ' + @error_message;

       -- Raise error, so the job that will call this procedure launches a notification
       RAISERROR (
                     @error_message, -- Message text. 
                     @error_severity, -- Severity. 
                     @error_state -- State. 
              );

END CATCH



No comments:

Post a Comment