August 19, 2014 at 8:44 am
I have a store proc that generates a list of individuals who need to get an email along with the subject and body of the email that each person should get. How do I step through the list from the stored proc and send an email to each person in the list.
This is the stored proc that creates the list who are to get an email...
SELECT e.Email, 'Deliverable Due' AS Subject, 'You have a deliverable due on - ' + d.DueDate + '. It is ' + d.Deliverable AS Body
FROM dbo.tblEmployeesResponsible INNER JOIN
CommonData.dbo.tblEmployee AS e ON dbo.tblEmployeesResponsible.EmpID = e.EmpID INNER JOIN
dbo.tblDeliverables AS d ON dbo.tblEmployeesResponsible.DelCatID = d.DelCatID
WHERE (dbo.ufn_DateValue(d.DueDate) = dbo.ufn_DateValue(GETDATE() - 7))
August 19, 2014 at 9:14 am
rodd.wilken (8/19/2014)
I have a store proc that generates a list of individuals who need to get an email along with the subject and body of the email that each person should get. How do I step through the list from the stored proc and send an email to each person in the list.This is the stored proc that creates the list who are to get an email...
SELECT e.Email, 'Deliverable Due' AS Subject, 'You have a deliverable due on - ' + d.DueDate + '. It is ' + d.Deliverable AS Body
FROM dbo.tblEmployeesResponsible INNER JOIN
CommonData.dbo.tblEmployee AS e ON dbo.tblEmployeesResponsible.EmpID = e.EmpID INNER JOIN
dbo.tblDeliverables AS d ON dbo.tblEmployeesResponsible.DelCatID = d.DelCatID
WHERE (dbo.ufn_DateValue(d.DueDate) = dbo.ufn_DateValue(GETDATE() - 7))
This is one of those rare times when a cursor is the right tool for the job. How you do this depends on a number of things. Are you doing this directly from sql server or is this being run from an application?
Also, what is the purpose of ufn_DateValue. Scalar functions are notoriously bad for performance. Using them wrapped around a column in your database like that forces your query to look at the results for each and every row.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2014 at 10:45 am
This is planned to run as a scheduled job in SQL Server.
August 19, 2014 at 11:11 am
rodd.wilken (8/19/2014)
This is planned to run as a scheduled job in SQL Server.
OK. What part(s) are you struggling with?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2014 at 11:47 am
declare
@email varchar(200),
@subject varchar(200),
@htmlBody varchar(max)
declare c1 cursor for
--#########################################################################
SELECT
e.Email,
'Deliverable Due' AS Subject,
'You have a deliverable due on - ' + d.DueDate + '. It is ' + d.Deliverable AS Body
FROM dbo.tblEmployeesResponsible
INNER JOIN CommonData.dbo.tblEmployee AS e
ON dbo.tblEmployeesResponsible.EmpID = e.EmpID
INNER JOIN dbo.tblDeliverables AS d
ON dbo.tblEmployeesResponsible.DelCatID = d.DelCatID
WHERE DATEADD(dd, DATEDIFF(dd,0,d.DueDate), 0) = DATEADD(dd, DATEDIFF(dd,0,getdate() -7), 0)
--#########################################################################
open c1
fetch next from c1 into @email,@subject,@htmlBody
While @@fetch_status <> -1
begin
--now the email itself:
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Your Database Mail',
@recipients=@email,
@subject = @subject,
@body = @htmlBody,
@body_format = 'HTML';
fetch next from c1 into @email,@subject,@htmlBody
end
close c1
deallocate c1
GO
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply