send_dbmail recipients from table

  • 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

  • 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

  • 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



    If you need to work better, try working less...

Viewing 3 posts - 1 through 2 (of 2 total)

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