If I had the limitations of Express, then personally I would use the CDO SMTP methods of VBScript, called using CScript (Windows Scripting Host), based on the successful / failed outcome of a script. The batch file envelope can be called using EXEC xp_cmdshell('c:\go.bat').

In English...

1. Create a batch file to launch your VBScript script.

go.bat

@echo off

cls

cscript /nologo myscript.vbs

2. Create your script file.

myscript.vbs

' Declare the object

Set Msg = CreateObject("CDO.Message")

' Set SMTP configuration here. You only need to point to your SMTP server and port number.

Msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

Msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.foo.com"

Msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

Msg.Configuration.Fields.Update

' Set e-mail details here.

Msg.Subject = "A problem has occurred with your script."

Msg.From = "mailbox@foo.com"

Msg.To = "you@foo.com"

Msg.TextBody = "Script has failed. Please check error log."

' Send the e-mail.

Msg.Send

3. Amend your SP to use the scripts.

ALTER PROCEDURE myProc (@name VARCHAR(100) )

AS BEGIN

SELECT [database_id] FROM sys.databases WHERE [name] = @name -- example query

END

becomes...

ALTER PROCEDURE myProc (@name VARCHAR(100) )

AS BEGIN

BEGIN TRY

SELECT [database_id] FROM sys.databases WHERE [name] = @name -- example query

END TRY

BEGIN CATCH

PRINT 'Something went wrong!'

EXEC xp_cmdshell 'c:\go.bat'

END CATCH

You can amend the VBScript / batch file scripts to take parameters if you like, e.g. define an error number / message and call go.bat as 'go.bat ' + @errorNumber, then amend go.bat to call the VBScript file with the %1 parameter passed in, so the VBScript script can put it in the subject of the e-mail, perhaps.

I've used this method before and yes, it's very 90s, but works absolutely fine.