Here's my stab at it.
<
/* Created by free online sql formatter: http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm */
/*This sets up a quick test table*/
IF object_id('DJmailDetails_T') > 0
DROP TABLE djmaildetails_t
CREATE TABLE djmaildetails_t (
id INT NOT NULL IDENTITY( 1 , 1 ),
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
emailaddress VARCHAR(100) NOT NULL,
sendmail BIT NOT NULL DEFAULT (0) )
INSERT INTO djmaildetails_t
(firstname,
lastname,
emailaddress,
sendmail)
VALUES ('dave',
'jackson',
'me@nonworkingExample.co.uk',
1)
INSERT INTO djmaildetails_t
(firstname,
lastname,
emailaddress,
sendmail)
VALUES ('fred',
'bloggs',
'you@nonworkingExample.co.uk',
1)
INSERT INTO djmaildetails_t
(firstname,
lastname,
emailaddress,
sendmail)
VALUES ('joe',
'smith',
'him@nonworkingExample.co.uk',
0)
INSERT INTO djmaildetails_t
(firstname,
lastname,
emailaddress,
sendmail)
VALUES ('mike',
'jones',
'her@nonworkingExample.co.uk',
1)
/*This selects from above table*/
DECLARE @recipients VARCHAR(4000)
SELECT @recipients = Coalesce(@recipients + ';','') + emailaddress
FROM djmaildetails_t
WHERE sendmail = 1
PRINT @recipients -- show what we got
PRINT '' -- a blank line
/***************************************************************************************************/
/*The next two variables are normally set in theerror handling of each 'bit' of DML in your SP/Script*/
DECLARE @Error_status INT
DECLARE @Error_message VARCHAR(128)
SET @Error_status = -1
SET @Error_message = 'failure updating something!' --something apposite
/*This should be in the error handler of the script or SP*/
ERRORHANDLER:
DECLARE @sql VARCHAR(8000)
DECLARE @mailMessage VARCHAR(128)
DECLARE @mailSubject VARCHAR(128)
DECLARE @Procedure_Name SYSNAME -- Holds the name of the currently executing procedure
SET @Procedure_Name = Object_name(@@PROCID) -- Set the procedure name in the variable
/*the next two lines would not be called above normally, so needs to be commented out here.
declare @recipients varchar(4000)
Select @recipients = COALESCE(@recipients+';' , '') + emailAddress from DJmailDetails_T where sendMail = 1
*/
/*This sends the message*/
SET @mailMessage = 'Table update failure' --something apposite
SET @mailSubject = 'Error in ' + Isnull(@Procedure_Name,'Ad-hoc Query')
SET @sql = 'exec master.dbo.xp_sendmail ''' + @recipients + ''',@message = ''' + @mailMessage +
''',@query = ''select ''''' + @error_message + ''''' as ErrorMessage,' + Isnull(CAST@Error_status AS VARCHAR),' ') + ' as ErrorNumber'',@subject = ''' + @mailSubject +
''',@dbuse = ''master'',@attach_results = false,@no_output = true,@width = 8000'
PRINT (@sql) --change this from print to an exec to 'really' do it.
--exec (@sql) --or uncomment this line
/*
Script to drop the table when finished testing
if object_id('DJmailDetails_T') > 0
drop table DJmailDetails_T
*/
I liked this so much I posted it on my site at http://glossopian.co.uk
Dave J