SQL Stored Procedure Loop for Job

try changing your call to sp_send_dbmail as follows to include the @query parameter,

EXEC msdb.dbo.sp_send_dbmail @recipients=N'ME@somewhere.com', @body= @msg,  @query = 'SELECT Field1, Field2, Field3 from Table1 where Complete = N''No''' ,@subject = 'SQL Server Trigger Mail', @profile_name = 'Profile Name'

in SQL 2005, You can also do something like this:

DECLARE @tableHTML  NVARCHAR(8000);
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
BEGIN
	DECLARE @msg varchar(500)
 
	SET @tableHTML = N'To confirm that you are getting an email on a particular date time "' + '. <br/><br/>' +
			N'<table border="1">' + 
			N'<tr><th>field 1</th><th>field 2</th><th>field 3</th>' +
			CAST ( ( SELECT td = UserName,       '',
	        			td = sum(case when Review_status = 'Assigned' then 1 else 0 end), '',
	                    		td = sum(case when Review_Status = 'Complete' then 1 else 0 end), ''
        	      		FROM SCH_A
				WHERE Complete = N'No'
	              		FOR XML PATH('tr'), TYPE 
    				) AS NVARCHAR(MAX) ) +
	    		N'</table>'
 
 
	EXEC msdb.dbo.sp_send_dbmail @recipients='yourmailrecipient@email.com',
			@subject = 'SQL Server Trigger Mail', 
			@profile_name = 'Profile Name',
			@body = @tableHTML,
		        @body_format = 'HTML' ;
 
 
	SET @AdditionalFilter = 0
	PRINT 'Message Sent!'
END
ELSE
BEGIN
	PRINT 'No Message Sent'
END

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:

wait, do you want to send one email for each record or all the records together?
if you want to send multiple mails you have to use cursor if one mail is enough you can use @query parameter as in my previous post.

if it's the way appari says, you can also have a look at this:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23800989.html?sfQueryTermInfo=1+each+email+member+send+sql+tabl

If you really don't want to use a Cursor you can create Function that sends the Mail
then Call the Function as column of the select,

While not getting to detailed here;

Select
  dbo.SendThatMail([Recip],'CompiledMEssage')
From
 Notices where Criteria is Met

>> If you really don't want to use a Cursor you can create Function that sends the Mail then Call the Function as column of the select,<<
You may want to double check that.  I suspect you will get the following error:
"Only functions and extended stored procedures can be executed from within a function."

First off, thanks for all of the feedback, I know now why I use this site!!

Many answers are close, but no cigar.  
From the top...

danrosenthal:  Maybe in there somewhere, but I'm very sorry, I should warn you I'm a bit of an amateur...  It definitely looks like it will be a cursor though, so you're on the right track for sure...

appari:  for some reason, the most basic email sent with the @query parameter doesnt seem to work for me.  But to answer your question, i would need separte emails for each record, sorry for not explaining better.  So, yes, it would need to be a Cursor I think...

ralmada:
Thanks for the tips, in the first one, it looks close, but as I forgot to mention, it would be each record being sent individually in the email.  The link looks good, it looks like this may be the path, but I'll keep it open to see if anyone else has any more specific ideas.

ONE MORE THING TO ALL...

I also forgot to mention that in this same table/View, it contains one email for each record.  So for each record, the email address it is being sent to is in the same record as the Field1, Field2, Field3 columns.  Not sure it that makes it easier or harder, but in case this helps im passing it along...

Again, thanks to everyone so far...

folderol:

I like it so far, where would I put the @msg declaration and where would I set that at?

acperkins:

You were right I got an error...

So all of the above will work.
Do you want one of these guys to write the Cursor for you?

acperkins :
I currently use this function method writing files but the fso File write method is also a function in SQL 2005

Something like this.  

I'm assuming the email address is within the table1, so I'm changing it for every email.

declare @nextid int
declare @lastid int
declare @selectcmd nvarchar(255)
declare @sentto nvarchar(100)
DECLARE @msg varchar(500)
SET @msg = 'To confirm that you are getting an email on a particular date time "' + '.'
 
CREATE TABLE #tmploop(
	id int identity(1,1) not null,
	key1 nvarchar(100) null,
	key2 nvarchar(100) null,
         recipient nvarchar(100) null
	)
CREATE INDEX[IX_loop_id] ON [#tmploop]([id]) ON [PRIMARY]
 
insert into #tmploop(key1, key2, recipient)
select Field1, Field2, email_address_field from Table1 where Complete = N'No'
 
select @lastid = max(id) from #tmploop
set @nextid = 1
 
while @nextid <= @lastid
BEGIN
  set @selectcmd = 'select field1, field2, field3 from #tmploop join table1 on key1=field1 and key2=field2 where #tmploop.id = ' + cast(@nextid as varchar)
  select @sentto = #tmploop.recipient from #tmploop where #tmploop.id = @nextid
  set @nextid = @nextid + 1
  EXEC msdb.dbo.sp_send_dbmail @recipients=@sentto, @body= @msg,  @query = @selectcmd ,@subject = 'SQL Server Trigger Mail', @profile_name = 'Profile Name'
END

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

folderol:
It looks like that will probably do it, sorry, I will try to test tomorrow am and let you know first thing.

Banthor:

If they were so inclined :).
Seriously, I know im asking for quite a bit here, but I just want to have a basic setup so that I can use this for many processes that could be substantially improved.  Im just starting to explore all of the possibilities that sql offers, and im pretty amazed at this point.  

I appreciate all of the help, and if folderol's solution works, i'll award points tomorrow.

OK, running into a dilemma, not sure where to go with this one.  If I use the @query parameter, no matter the sql statement, it gives an error msg stating that it cannot find user "My Username".   I tried @attach_query_result_as_file = 1 with the same result.  This only happens if I use the @query parameter, so Im sorry, but apparently Im still puzzled here.  I'll continue to work with what I have so far, and if anyone has any further input Im all ears.

i would need to see your code in more detail, but the query parameter does not support any passing of parameters to it or references to columns from the connection which calls it because it runs as a new session.  That's why my snippet uses the syntax CAST(@nextid as varchar).

I tested and that worked ok for me.

OK, narrowed it mostly down now.

Can include a query, as I wasn't including the full path to the database, so it wasnt finding the table ([database].[schema].[tablename]).  Sorry, thats my bad, never had to do that on this server before.

However, it hangs if I attempt to use the following statement, with tablenames changed of course...

set @selectcmd = 'select field1, field2, field3 from #tmploop join Alerts.dbo.table1 on key1=field1 and key2=field2 where #tmploop.id = ' + cast(@nextid as varchar)
select @sentto = #tmploop.recipient from #tmploop where #tmploop.id = @nextid


Thoughts??

set @selectcmd = 'select field1, field2, field3 from #tmploop join Alerts.dbo.table1 on key1=field1 and key2=field2 where #tmploop.id = ' + cast(@nextid as varchar)
select @sentto = #tmploop.recipient from #tmploop where #tmploop.id = @nextid

                                              

1:
2:

The effort displayed in trying to help me, a newbie, was excellent.  Gave me all the ideas I needed to solve what should be a simple issue.  Thanks.

OK, the solution, it looks like, was semi-simple, but all I could figure was to use a Cursor and set it up like this:

DECLARE @Field1 nvarchar(70)
DECLARE @Field2 nvarchar(50)
DECLARE @Email nvarchar(250)
DECLARE cursorName CURSOR for
Select Field3, Field2, Email from Alerts.dbo.Table1 where Complete = N'No'
OPEN cursorName
FETCH NEXT FROM cursorName
 INTO @Field1, @Field2, @Email
 While @@FETCH_STATUS = 0
 Begin
  EXEC msdb.dbo.sp_send_dbmail
@recipients=@Email,
@body= @Field1,  
@subject = @Field2,
@profile_name = 'MY PROFILE'
  FETCH NEXT FROM cursorName
  INTO @Field1, @Field2, @Email
 End

folderol:  You spent much time and effort in helping me, and for that I'm giving you 200 points.  I think you were probably dead on, but a little advanced for what I was looking for.

danrosenthal:  You gave the best link for a Cursor example, so thank you.

Thank you to everyone, very well supported!

Your code looks perfect. Great work!