Trying to use sp_send_dbmail to loop through a query and send email for each record it finds.

since the Email address are stored in table :PPtblTransactions, u may run thro' a cursor to concatenate all the emailaddress and store it in a variable say @emailaddress and then set the
EXEC msdb.dbo.sp_send_dbmail
     @recipients =@emailaddress

hope this help you.

Any idea what this would look like? I'm fairly new to MS SQL...

i have a table tbusers which store the emailid of users,
i have created a curosr to concatenate emailaddress with semicolon as delimiter.
the variable @concat will have all the emailid's once the loop completes

declare @emailAddress varchar(50)
declare @concat varchar(1000)

declare Maillist cursor
for select distinct top 5 Email from tbusers
      OPEN Maillist    
FETCH FROM Maillist into @emailAddress

WHILE NOT (@@fetch_status=-1)    
 BEGIN    
        IF len(@concat)>0  
            begin
                  select @concat = @concat +';'+@emailAddress
            end
        else
            begin
                  select @concat = @emailAddress
            end
        FETCH NEXT FROM Maillist INTO @emailAddress    
 END    
   
CLOSE maillist    
DEALLOCATE Maillist  

begin
set nocount on
select TRXNMBR, EMAIL from MCGSQL.dbo.PPtblTransactions where STATUS = '0'
if @@ROWCOUNT >0
begin
EXEC msdb.dbo.sp_send_dbmail
    @recipients = @concat,
    @query = 'select * from MCGSQL.dbo.PPtblTransactions' ,
    @subject = 'Unprocessed Order',
    @attach_query_result_as_file = 0 ;
end
end

print @concat

I'm still not sure how this is going to work... You're saying to create a table called tbusers and insert the email addresses there? The user is specifying the address when the record is written to PPtblTransactions... So I need to insert it into tbusers at the same time? Is there not another way?

the table tbusers was an example to get details and concatenate
you may make use of ur Table : select distinct Email from PPtblTransactions to retrive the details of the emailid
instead of this statement select distinct top 5 Email from tbusers

That get's me almost there. I'm trying to only send the user the details of their own transaction.

As the code is today each user gets every other users records in the body of the message. Is there a way to prevent this?

declare @emailAddress varchar(50)
declare @concat varchar(1000)

declare Maillist cursor
for select distinct EMAIL from PPtblTransactions where STATUS = '0'
      OPEN Maillist    
FETCH FROM Maillist into @emailAddress

WHILE NOT (@@fetch_status=-1)    
 BEGIN    
        IF len(@concat)>0  
            begin
                  select @concat = @concat +';'+@emailAddress
            end
        else
            begin
                  select @concat = @emailAddress
            end
        FETCH NEXT FROM Maillist INTO @emailAddress    
 END    
   
CLOSE Maillist    
DEALLOCATE Maillist  

begin
set nocount on
select TRXNMBR, EMAIL from MCGSQL.dbo.PPtblTransactions where STATUS = '0'
if @@ROWCOUNT >0
begin
EXEC msdb.dbo.sp_send_dbmail
    @recipients = @concat,
    @query = 'select * from MCGSQL.dbo.PPtblTransactions' ,
    @subject = 'Unprocessed Order',
    @attach_query_result_as_file = 0 ;
end
end

print @concat

That doesn't change the results. I think I need to change the " @query = 'select * from MCGSQL.dbo.PPtblTransactions ' , " However I cant pass @emailAddress to sp_send_dbmail