September 17, 2013 at 4:36 am
Hi All,
my first post here maybe a newby one.
Well,
i have a table with data as from example below.
statuscd|ordernum|name|date|email|sent|
OK|22112233|john|17/09/2013|john@john.com|N|
OK|56489161|mike|16/09/2013|mike@mike.com|N|
...
...
Idea is that i need to send an email to this recipients with sent flag N. For example john@john.com and to put in subject ordernum and statuscd and after that to update sent flag to Y because query will select data with flag N and send it again.
This is recursive process because this table is refreshing every 15 minutes and email job will start every 10 minutes.
Many thanks in advance.
Regards
September 17, 2013 at 6:59 am
Hi Djmarkoos
Imho you can try in such way
--Temp table for test
SELECT 'OK' AS statuscd, 22112233 AS ordernum, 'john' AS name,'17/09/2013' AS dateord, 'john@john.com' AS email, 'N' AS sentid
INTO #dbm;
INSERT INTO #dbm
SELECT 'OK',56489161,'mike','16/09/2013','mike@mike.com','N';
DECLARE kursor CURSOR FOR
SELECT ordernum
FROM #dbm WHERE sentid = 'N'
OPEN kursor
DECLARE @ordr int
FETCH NEXT FROM kursor INTO @ordr
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @e_mail varchar(50)
DECLARE @flag char(2)
DECLARE @id varchar(25)
SELECT @e_mail = email,@flag = statuscd,@id = CAST(ordernum as varchar(25)) FROM #dbm WHERE ordernum = @ordr
DECLARE @body1 varchar(100)
SET @body1 = 'Order num:'+@id+ ' ,current status: '+ @flag+ ''
EXEC msdb.dbo.sp_send_dbmail @recipients=@e_mail,
@subject = 'Order status',
@body = @body1,
@body_format = 'HTML'
FETCH NEXT FROM kursor INTO @ordr
END
CLOSE kursor
DEALLOCATE kursor
Br.
Mike
September 17, 2013 at 7:37 am
To avoid having 2 processes using the same row you can use a SELECT for UPDATE:
DECLARE @email VARCHAR(100), @subject VARCHAR(100), @body VARCHAR(500)
DECLARE @tbl TABLE (EmailSubject VARCHAR(100), EmailTo VARCHAR(100), EmailBody VARCHAR(500))
WHILE EXISTS (SELECT 1 FROM #dbm WHERE sentid = 'N')
BEGIN
UPDATE t SET sentid = 'Y' OUTPUT INSERTED.esub, INSERTED.email, INSERTED.ebody INTO @tbl FROM (SELECT TOP 1 'Order ' + CAST(ordernum AS VARCHAR(20)) + ' - ' + statuscd AS esub, email, 'Dear ' + name + ' .....' AS ebody, sentid FROM #dbm WHERE sentid = 'N') t
SELECT @email = Emailto, @body = EmailBody, @subject = EmailSubject FROM @tbl
EXEC msdb.dbo.sp_send_dbmail @recipients=@email, @subject = @subject, @body = @body, @body_format = 'HTML'
DELETE FROM @tbl
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply