SQL Server email queue

Question

I have an orders table which is populated with new orders frequently

Orders table:

OrderID    OrderName    EmailAddress        Status
-----------------------------------------------------
1          iphone       test@gmail.co.uk    New
2          samsung      nw@gmail.com        New
3          nexus        f@gmail.com         Approved

For every order line which has status = 'New', I would like to set up a job to run every 30 minutes send an email to those recipients.

An almost similar [question](http://stackoverflow.com/questions/36468687/how-to-perform-an-action-on-one-result-at-a-time-in-a-sql-query-return-that-shou/36469391#36469391) was asked yesterday, You can combine that with a scheduled job — t-clausen.dk, Apr 08 '16 at 10:04

score 0 · Accepted Answer · answered Apr 08 '16 at 10:41

As per my understanding, you need to schedule a job which send an email notification if order status is 'New'.

If so create the following stored procedure

CREATE PROCEDURE NewOrders_Job
AS
BEGIN
    DECLARE @OrderName varchar(200), @EmailAddress varchar(200)
    SELECT
        @OrderName = ' You have a sale for '+[OrderName]+', at +'CONVERT(VARCHAR(10),GETDATE(),)'+'
        ,@EmailAddress = [EmailAddress]
    FROM [Orders table]
    WHERE 
        [Status] = 'New'


    IF(ISNULL(@EmailAddress,'')<>'')
    BEGIN
        --mail
        DECLARE
         @profile_name VARCHAR(200)
        ,@recipients VARCHAR(MAX)
        ,@copy_recipients VARCHAR(MAX)
        ,@blind_copy_recipients VARCHAR(MAX)
        ,@body VARCHAR(MAX)
        ,@body_format VARCHAR(MAX)
        ,@subject VARCHAR(MAX)  


        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = 'Your_Profile_Name'  
            ,@recipients = @EmailAddress  
            ,@copy_recipients = 'youremail@domain.com'
            ,@blind_copy_recipients = 'admin@yourdomain.com'
            ,@body = @OrderName 
            ,@body_format = 'HTML'  
            ,@subject = 'You have a new sales Order'; 
    END
END

now, create a new job which run's for 30 mins with above stored procedure

SQL Server email queue

1 Answers1