How to perform an action on one result at a time in a sql query return that should return multiple results?

Question

I am trying to send emails to all the addresses in a database that fit a certain description. My query will look something like this:

SELECT EmailAddress 
  FROM Customers` 
 WHERE EmailFlag = 'true'` 
   AND (Today'sDate - DateOfVisit) >= 90;

Not sure how I would do Today's Date - DateOfVisit, maybe you can help with this too?

My real question is how to take the results of this query pop one email address off of the top, use it to send an email, set it's flag to false, run the query again repeat until the query returns null.

Thanks in advance for your help!

Well, almost any ORM would return you an `IEnumerable` for this query. So it would literally be a `foreach(var email in emails) { sendEmail(email); }`. As for dates, look into `DATEDIFF` and `GETDATE()` — Rob, Apr 07 '16 at 06:53
@t-clausen.dk I agree the `dateadd` approach is better, nice catch. Though I'd be hesitant to say looping has been avoided - because a loop will be done in the background. — Rob, Apr 07 '16 at 10:52
@Rob I agree that some looping is happening somewhere. I imagine it is optimal to let the mail system handle the looping, My code is not doing the looping. My history log will only gets one entry. — t-clausen.dk, Apr 07 '16 at 11:09
The premise of the program is to take the date that a customer was there last (which i will update in a different part of the program) and compare it to today's date and see if it is greater than or equal to 90 days. — theletterzee, Apr 07 '16 at 18:19
@AdrianoRepetti, I'm new so could you tell me how better to ask this? :) — theletterzee, Apr 08 '16 at 04:35

score 0 · Accepted Answer · answered Apr 07 '16 at 07:00

Use a loop to iterate through the results of your query.

SELECT EmailAddress 
FROM Customers` 
WHERE EmailFlag = 'true'` 
AND DATEDIFF(day, GETDATE(),DateOfVisit) >= 90;

Replace day with other units you want to get the difference in, like second, minute etc.

c#:

foreach(DataRow dr in queryResult.Tables[0].Rows)
{
   string email = dr["EmailAddress"].ToString();
   // Code to send email
   //Execute Query UPDATE Customers SET EmailFlag = False WHERE EmailAddress = email 
}

This is just a draft. You should replace the comments with the actual code to make it work. No need to fetch from your initial query 1 result at a time.

This is what I needed to know. Except for the date comparison, in which case I would use t-clausen's suggestion of WHERE DateOfVisit < dateadd(day, -90, getdate()) — theletterzee, Apr 07 '16 at 21:21

score 0 · Answer 2 · answered Apr 07 '16 at 07:04

For the dates, use functions like DATEDIFF() and GETDATE() or GETUTCDATE().

SELECT EmailAddress 
FROM Customers` 
WHERE EmailFlag = 'true'` 
AND DATEDIFF(day, GETDATE(),@dateOfVisit) >= 90;

Now to get the email addresses, you can use any ORM, or you can write your own DAL using ADO.NET. From my experience, I would suggest using dapper. Its simple to use and very efficient.

In that case you would need only:

string email = connection.Query<string>("your_proc_name", new { dateOfVisit = dateOfVisit }, commandType: System.Data.CommandType.StoredProcedure).First();
//now write another proc to reset the flag

score 0 · Answer 3 · edited May 23 '17 at 12:24

The correct way to compare the dates is this:

WHERE 
  DateOfVisit < dateadd(day, -90, getdate())

This script will send the mails, you need to configure a profile:

DECLARE @sendto varchar(max) 


SET @sendto = STUFF(( 
        SELECT ';' + [EmailAddress ] 
        FROM Customers
        WHERE DateOfVisit < dateadd(day, -90, cast(getdate() as date))
        for xml path(''), type 
    ).value('.', 'varchar(max)'), 1, 1, '') [values]

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'profile name you need to configure',
@blind_copy_recipients = @sendto,
@body = 'Body text',
@subject = 'Subject text';

This is not sargable and should be avoided:

DATEDIFF(day, @dateOfVisit, GETDATE()) >= 90;

thank you for your input and explaining what sargable means :P I've never heard of it before. — theletterzee, Apr 07 '16 at 18:22

score 0 · Answer 4 · answered Apr 08 '16 at 05:11

I found that there is such a thing as a SELECT LIMIT statement in sql that allows you to specify how many results you want returned at once. It may look like this:

SELECT expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
LIMIT number_rows [ OFFSET offset_value ];

How to perform an action on one result at a time in a sql query return that should return multiple results?

4 Answers4

Linked