SQL Stored Procedure Loop for Job
Posted on 2009-07-02
Hi. I am currently struggling with a SQL Procedure where I am attempting to send an email by a SQL Job if database Criteria is met. Currently, I have the following test syntax, which works fine.
DECLARE @AdditionalFilter BIT
DECLARE @MyDate As smalldatetime
SET @AdditionalFilter = 1
SET @MyDate = convert(varchar(10), ('07/02/2009'))
IF @AdditionalFilter = 1 AND convert(varchar(10), (getdate()), 101) = @MyDate
DECLARE @msg varchar(500)
SET @msg = 'To confirm that you are getting an email on a particular date time "' + '.'
EXEC msdb.dbo.sp_send_dbmail @recipients=N'ME@somewhere.com', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'Profile Name'
SET @AdditionalFilter = 0
PRINT 'Message Sent!'
PRINT 'No Message Sent'
This simply sends a message to one recipient if the date is found to be today. Simple enough, I will just put this into a SQL Job and be good.
Problem comes in that I want to insert another criteria so that if in the following SQL Statement:
SELECT Field1, Field2, Field3 from Table1 where Complete = N'No';
All values in the table that are found to have 'No' for their value (It is an nvarchar(4) field, not boolean), will be sent an email via that same job. So basically I'm asking for a Loop, but I understand that a SQL "Loop" may not be quite what I'm looking for. I've searched all around and found close to what I'm looking for but not quite enough to get me going.
I guess I'm looking for possibly a CURSOR statement, or simply something that takes the original procedure and adds values of Field1, Field2, Field3 to the email from records contained in 'Table1' where Complete = 'No'. If I'm going about anything wrong I apologize, this is still somewhat new to me.
Thank you for any assistance.