Is it possible to have
SQL Server (2008)
High level overview of how you can do this..
1.Modify stored Procs to Return 1 in case of failure and log into table.This also has some weakness because some failures wont be Caught
2.Once the information is logged into table,then sending email is easy using SP_SEND_DBMAIL.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Adventure Works Administrator', @recipients = 'yourfriend@Adventure-Works.com', @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder WHERE DueDate > ''2004-04-30'' AND DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' , @subject = 'stored procedures Failures'