Automates sending email using SQL Server 2008 T-SQL, with custom message title and body

Question

This question is the follow-up of this thread

Given that I've created two simple tables User and Item:

• User (Id, Name, Email)
• Item (Id, CreatedDate, EmailSent, UserId)

I am able to create an SQL Server Agent job that periodically runs the following script:

USE test
DECLARE @emails varchar(1000)
SET @emails = STUFF((SELECT ';' + u.Email FROM [user] u JOIN [item] i 
ON u.ID = i.UserId 
WHERE i.EmailSent = 0 AND DATEDIFF(day, i.CreatedDate, GETDATE()) >= 30 
FOR XML PATH('')),1,1,'')

/** send emails **/
EXEC msdb.dbo.sp_send_dbmail 
@profile_name='test',
@recipients=@emails,
@subject='Test message',
@body='This is the body of the test message.'

The purpose is to get any item that has been created for >= 30 days and then send a reminder email to its user. The EmailSent is checked first to exclude items already reminded.

I want to improve this script as follows:

1. There's a separate query that does the SELECT part and stores the result into a variable so that it can be reused in the email sending query, and in an UPDATE query that sets selected items' EmailSent to True (done after emails sent).
2. Customise the title and body of the message with user's Name and item's Id. Something like: Dear Name, the item Id has been created for 30 days or more.

Anybody has any idea of how these improvement could be done?

Answer 1

I've managed to have this so far

USE test
-- 1) Update selected items to state 1
UPDATE [PickingList]
SET ReminderState = 1
WHERE ReminderState = 0 AND DATEDIFF(day, CreatedDate, GETDATE()) >= 30

DECLARE @userId INT
DECLARE @email NVARCHAR(100)
DECLARE @plId INT
DECLARE @getId CURSOR

-- 2) Process those having state 1
SET @getId = CURSOR FOR
SELECT u.ID, u.Email, pl.ID
FROM [User] u JOIN [PickingList] pl
ON u.ID = pl.UserId
WHERE pl.ReminderState = 1

OPEN @getId
FETCH NEXT
FROM @getId INTO @userId, @email, @plId
WHILE @@FETCH_STATUS = 0
BEGIN
    /** send emails **/
    DECLARE @pId VARCHAR(1000)
    DECLARE @title VARCHAR(1000)
    DECLARE @body VARCHAR(8000)
    SET @pId = CAST(@plId AS VARCHAR(16))
    SET @title = @pId + ' was created more than 30 days ago'
    SET @body = 'The following picking list ' + @pId + ' blah blah'

    DECLARE @code INT
    SET @code = -1
    EXEC @code = msdb.dbo.sp_send_dbmail 
    @profile_name='test',
    @recipients=@email,
    @subject=@title,
    @body=@body

    -- 3) Log the email sent and update state to 2
    -- Below is what I want to do, but ONLY when the it can be sure that 
    -- the email has been delivered
    INSERT INTO [PickingListEmail] (UserId, PickingListId, SentOn) 
    VALUES (@userId, @plId, GETDATE())
    UPDATE [PickingList] SET ReminderState = 2 WHERE ReminderState = 1

    FETCH NEXT
    FROM @getId INTO @userId, @email, @plId
END
CLOSE @getId
DEALLOCATE @getId

In step (3), before saving the email sent and update the item to state 2 (processed), I would want to make sure that the email has been sent, based on the data fetched from sysmail_log, as sp_send_dbmail would only care about whether it can send the mail to the SMTP server, so will return the success code 0 even when the sending fails.

Something like this:

meaning of the values of sent_status on msdb.dbo.sysmail_mailitems

or

Check If sp_send_dbmail Was Successful