Trying to use sp_send_dbmail to loop through a query and send email for each record it finds.
Posted on 2008-10-14
I am attempting to write a query that allows me to send a message to a user based on a query result.
I've got a table to Transactions (PPtblTransactions) that holds transactions that are entered into the database. Each record has an EMAIL, EXDATE and STATUS field. I want to send a message to each EMAIL address when the status = '0' and the EXDATE is within one day. I need to be able to schedule this and run at specific times
So here's what I've got so far. I've not put a restriction in for the 1 day limit at this point.
set nocount on
select TRXNMBR, EMAIL from MCGSQL.dbo.PPtblTransactions where STATUS = '0'
if @@ROWCOUNT >0
@recipients = '<<Not sure how to insert the EMAIL field here>>',
@query = 'select * from MCGSQL.dbo.PPtblTransactions' ,
@subject = 'Unprocessed Order',
@attach_query_result_as_file = 0 ;
Any help would be great.