Sending Automatic Emails from SQL server

I am excited to reactivate my blog after 2.5 years.

I have written a stored procedure last week to send automated emails to customers based on certain criteria.

To identify this there is a stored procedure that requires some parameters.

The steps followed for creating the stored procedure are as follows:

• There is already an existing stored procedure that creates a temp table and inserts data into that temp table.
• In the new stored procedure define a new temp table.
• Insert data into this temp table by executing the existing stored procedure
• Declare new variables to hold the needed for the email.
• In a while loop assign the data to the above variables for each record and send the email one by one using sp_send_dbmail built in stored procedure.

Here is the skeleton code I came up with for the procedure.  I have configured a HTML email

[code]

Declare @temp_table table
(
ID INT IDENTITY(1, 1) primary key ,
  field1 varchar(60) null,
AccountID VARCHAR(25) null,
AccountName varchar(200) null,
AccountEMail varchar(128) null
       )

--
-- Insert into the temp table variable -- the list to be emailed by running the stored procedure Proc1
--

Insert into @temp_table
(

  field1,
AccountID,
AccountName,
AccountEMail

)
Exec [Proc1]
@Parameter1 = 'NZIO',
@Parameter2 = 1,
@Parameter3=0


--
-- Declare new variables
--

DECLARE @count INT
DECLARE @accountemail varchar(60)
DECLARE @accountname varchar(60)
DECLARE @rowcount INT
DECLARE @href1 varchar(256)

--setting count to start from first row
SET @count = 1
SET @rowcount = (SELECT count(*) FROM @temp_table)
--Print @rowcount


--
--loop to send email from temp table
        --

WHILE(@count < @rowcount)
BEGIN
SET @accountemail = (SELECT AccountEMail FROM @temp_table where Id = @count and AccountEMail LIKE '%_@__%.__%')
SET @accountname = (SELECT AccountName FROM @temp_table where Id = @count and AccountEMail LIKE '%_@__%.__%')
SET @href1 = (SELECT CASE When ClientMode = 'UNATTENDED (HANDS-FREE)' then 'http://link1.pdf' else 'http://link.pdf' END FROM @temp_table where Id = @count and AccountEMail LIKE '%_@__%.__%')


--
--Configure Email body
--

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
N'

Automatic Notification -- Please do not reply

' +
N'Good Morning  ' +
cast(@accountname as nvarchar(max)) +
N' We are contacting you to let you know that blah blah (EDI: ' +
cast(@field1 as nvarchar(max)) +
N')
is not working.  Please have a look at your account+ cast(@accountname as nvarchar(max)) +
N' Please click on the below link to view user guide on starting
' + N' Userguide for Windows ' +
N' Regards BI Support '

;

--
-- Send email using Database Mail
--

--USE msdb
--GO
EXEC MSDB.dbo.sp_send_dbmail @profile_name='Testreport',
@copy_recipients = @accountemail,
@recipients = 'iemail@emailadd.com',
@subject='Test message',
@body= @tableHTML,
@body_format = 'HTML' ;



SET @count= @count +1

END


[/code]