How to iterate through table for an sql job?

Question

PerformanceReview

• prID
• reviewDate
• passed
• notes
• successStrategy
• empID
• nextReviewDate

above is my table I am working with, my goal is to get the nextReviewDate check to see if it is within 7 days of the current date ( I will do this using DATEDIFF() ) and send an email to a specified email address if this condition is true.

My question is, how do I make it so that my sql job will perform this task for each performance review row in the table. I have researched and found information on CURSORS, or using WHILE loops being slow and inefficient for this task. Any help is appreciated as I am in the final stage of development :)

Answer 1

If you are within a SQL Server context and you want to send the mails using sp_send_dbmail, using a CURSOR to loop through the rows and call sp_send_dbmail is just fine. It may not be the fastest but it in this case it won't matter all that much. You are not looking to shave off milliseconds for this sort of process.

It will be a lot more of a hassle to formulate a set-based approach. This would involve creating a dynamic SQL statement to have all the sp_send_dbmail calls in one batch. But the gain would be marginal.