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]
