backup job with proper error message

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[BackupProcess]    Script Date: 5/31/2018 4:34:46 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Naveen Gupta
-- Create date: 10-Nov-2015
-- Description: To Take the backup of required databases
-- =============================================

ALTER PROCEDURE [dbo].[BackupProcess]

AS

BEGIN

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name
Declare @SQL varchar(1000)=''
declare @begintime nvarchar(100) ='05/31/2018'
declare @result varchar(max)
-- specify database backup directory
SET @path = 'D:\MSSQL\Backup\' 


-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')


DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases where name in ('pOrbisNAFTA')
--WHERE name IN ('master','model','msdb','tempdb')  -- exclude these databases


OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 


WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
select @fileName,@name
BEGIN Try
--BACKUP DATABASE @name TO DISK = @fileName  WITH NOFORMAT, NOINIT,  SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
select @fileName,@name
BACKUP DATABASE @name TO DISK = @fileName  WITH NOFORMAT, NOINIT,  SKIP, NOREWIND, NOUNLOAD,   STATS = 10
SET @SQL = 'insert into ' + @name +'.dbo.EmailSchedule(Subject,emailto,emailcc,Body) select ''Backup'',''narenderp@damcogroup.com'',''GauravH@damcogroup.com'',''Backup of database ' + convert(varchar(100),@name) + ' has been done successfully.'''
EXEC(@sql)
END TRY
BEGIN CAtch

SELECT ERROR_MESSAGE(),ERROR_SEVERITY(), ERROR_STATE(),ERROR_PROCEDURE()
IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (LogDate datetime,ProcessInfo nvarchar(100),LogText nvarchar(4000))
INSERT #Results
EXEC  xp_readerrorlog  0, 1, N'Backup',@name,@begintime

SELECT @result = LogText from #Results where ProcessInfo = 'spid'+cast(@@SPID as varchar(6)) order by logdate desc

SET @SQL = 'insert into ' + @name +'.dbo.EmailSchedule(Subject,emailto,emailcc,Body) select ''Backup'',''narenderp@damcogroup.com'',''GauravH@damcogroup.com'',''Backup of database ' + convert(varchar(max),@result) + ' has been failed.'''
SELECT @result
EXEC(@sql)
END Catch
FETCH NEXT FROM db_cursor INTO @name 
END 
CLOSE db_cursor 
DEALLOCATE db_cursor

END