SQL SERVER TRY CATCH BLOCK SAMPLES

SQL SERVER TRY CATCH BLOCK SAMPLES

/************************************************************************/
IF @Err = 1
BEGIN
 exec msdb.dbo.sp_send_dbmail
 @profile_name='ReportingMsgs'
 ,@recipients='Alerts@mydomain.com;DataOperations@mydomain.com'
 ,@subject='Error occurred in the SuperAwesome SQL job run'
 ,@body_format='HTML'
 ,@body=@ErrMsg
END
/************************************************************************/
BEGIN CATCH
          SELECT @ErrorMessage = ERROR_MESSAGE();
          RAISERROR (@ErrorMessage, 16, 1);
          RETURN
END CATCH
/************************************************************************/
BEGIN CATCH
  SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();
  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState );
  RETURN
END CATCH
/************************************************************************/
BEGIN CATCH
  RAISERROR('Unable to add new client', 16, 1)
        RETURN
END CATCH
/************************************************************************/
begin catch
  declare @msg varchar(max) = 'A DM Failed. Error Message: ' + error_message()
  exec SUPPORT.dbo.ReportErrorEmail
   'DM Failure'
   , @msg
   ,'Alerts@mydomain.com;DataOperations@mydomain.com'
end catch
/************************************************************************/

BEGIN CATCH
  DECLARE @error2 varchar(max)
  DECLARE @msg2 varchar(max)

  SELECT @error2 = ERROR_MESSAGE();

  SET @msg2 = 'Exception occurred in proc, '+ @@servername + '.'+db_name() +'.dbo.BillingForEntity, when processing BillingEntity ' + @BillingEntity
     + '. Exception detail: ' + @error2
  EXEC msdb.dbo.sp_send_dbmail
  @profile_name='ReportingMsgs',
  @recipients='Alerts@mydomain.com;DataOperations@mydomain.com',
  @subject='Billing Invoice Create Exception',
  @body=@msg2,
  @importance='High'
            
        RETURN;
END CATCH
/************************************************************************/
BEGIN CATCH
     if(ERROR_NUMBER()=242) or (ERROR_Message() like '%date%')
  begin

   select 'Line '+@noRecordsChar +':Invalid Invoice Date detected.This is not editable cell.';
  end
  else
  begin
        Select 'Error Occured WHile Validating The Payment Records.';
  end

END CATCH
/************************************************************************/