How can you send mail on an exception raised in procedure?

Mail can be sent via SQL server only if SMTP is set on the server. Post that you can send the mails as the normal mail, below is the example by which you can send a mail when exception is raised:

Below is the sample procedure for sending a mail :

alter proc usp_exception_handle

  as

  begin try

 select 2

RAISERROR('User define error',16,1)

  end try

BEGIN CATCH

         DECLARE @ERROR_MESSAGE NVARCHAR(MAX), @mysubject NVARCHAR(MAX) ;

  set @mysubject =N' Error Rasied on Server: '+@@SERVERNAME+' in Database: '+DB_name()+' in Procedure '+ERROR_PROCEDURE()+' '

SET @ERROR_MESSAGE =

 N' Error Rasied on Server: '+@@SERVERNAME+' in Database: '+DB_name()+' in Procedure '+ERROR_PROCEDURE()+' ' +

 N'ERROR_NUMBER: '+cast(ERROR_NUMBER() as nvarchar(30))+

' ERROR_STATE: '+cast(ERROR_STATE()as nvarchar(30))

+' ERROR_SEVERITY: '+ cast(ERROR_SEVERITY()as nvarchar(30))+N' ERROR_LINE '+cast(ERROR_LINE()as nvarchar(30))

+ ' ERROR_MESSAGE: '+ ERROR_MESSAGE()+ ' Error Date Time: ' + ''+cast(GETDATE()as nvarchar(30))

EXEC msdb.dbo.sp_send_dbmail

@profile_name = '@profile_name',

@recipients = 'ABCD@gmail.com',

@body = @ERROR_MESSAGE,

@subject = @mysubject ,

@body_format = 'HTML' ;

END CATCH