Loop Through the records - send email - set the value of the field - Need Code Help

Question

Post reply

Loop Through the records - send email - set the value of the field - Need Code Help

skb 44459

SSCarpal Tunnel

Points: 4768
More actions
October 19, 2012 at 5:57 am

#393425

-- I am hoping not to use cursor
-- Query is
select * from Orders where EmailSent = 0
--I want to loop through records with above query send email and Set Emailsent field
--Email Code
EXEC msdb.dbo.sp_send_dbmail
@profile_Name ='Administrator',
@recipients= Orders.CustomerEmailAddress ,
@subject = 'Please send Feedback for Order#' + Orders.OrderNum ,
@body = 'Please send Feedback for Order#' + Orders.OrderNum
--Update Field
set Orders.EmailSent = 1

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

here's an example of what you are asking;

you'll have to use a cursor if you want to send emails on a per-email basis from the data.

remember that when you assign parameters, you cannot concatent/use functions/manipulate anything in the assignment, that has to occur BEFORE you assign it.

--so this is bad:

EXEC msdb.dbo.sp_send_dbmail

@subject = 'Please send Feedback for Order#' + Orders.OrderNum

--this is the correct way:

declare @var varchar(128)

SET @var ='Please send Feedback for Order#' + Orders.OrderNum

EXEC msdb.dbo.sp_send_dbmail

@subject =@var

and here's the full exmaple:

DECLARE

@isql VARCHAR(2000),

@email VARCHAR(64),

@order VARCHAR(64),

@emailSubject varchar(128),

@emailBody varchar(128)

DECLARE c1 CURSOR FOR

SELECT

CustomerEmailAddress,

OrderNum

FROM Orders

WHERE EmailSent = 0

OPEN c1

FETCH NEXT FROM c1 INTO @email,@order

WHILE @@FETCH_STATUS <> -1

BEGIN

SELECT

@emailSubject = 'Please send Feedback for Order#' + @order,

@emailBody = @emailSubject --you'd build a bigger, more descriptive string for the body.

--you cannot concatenate strings as the parameters for a query, they must be eitehr a static string or an already built variable.

EXEC msdb.dbo.sp_send_dbmail

@profile_Name ='Administrator',

@recipients= @email ,

@subject = @emailSubject,

@body = @emailBody

FETCH NEXT FROM c1 INTO @email,@order

END

CLOSE c1

DEALLOCATE c1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

skb 44459 SSCarpal Tunnel Points: 4768 More actions · Answer 2

Thanks for the reply.

Another Quick Question. My Email body is very long.

Ideally i would like to store the Body in a text file and read the text file into a SQL Variable like