Sending email from store proc

  • 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))

  • 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/

  • This is planned to run as a scheduled job in SQL Server.

  • 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/

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply