Using sp_send_dbmail with recieptent and content from single sql row

Question

I have a table filled with oldUserID, newUserID, name and email. I want to use sp_send_dbmail to the email on each row. For example:

oldUserID | newUserID | name | email

21213125 | 2355233571 | Tom | [email protected]

65465465 | 4564884664 | Mat | [email protected]

And so on for 200 rows. Is there any way to send an sp_send_dbmail to the email on each row including oldUserID and newUserID? The output in the mail would be something like:

"Your old user id: 21213125, your new user id: 2355233571"

I would appreciate not to enter each emailadress manually.

Thank you!

Yes there is a way. Have you googled a solution and attempted it? — dfundako
– dfundako, Commented May 17, 2016 at 16:00

Devart · Accepted Answer · 2016-05-17 15:08:13Z

2

DECLARE
      @txt NVARCHAR(MAX)
    , @name NVARCHAR(60)
    , @email VARCHAR(100)

DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
    SELECT 'Your old user id: ' + CAST(oldUserID AS NVARCHAR(100)) 
         + ', your new user id: ' + CAST(newUserID AS NVARCHAR(100)), name, email
    FROM ...

OPEN cur

FETCH NEXT FROM cur INTO @txt, @name, @email

    WHILE @@fetch_status = 0
    BEGIN

        EXEC msdb.dbo.sp_send_dbmail @profile_name = ...
                                   , @recipients = @email
                                   , @subject = @name
                                   , @body = @txt
                                   , @body_format = 'HTML'

    FETCH NEXT FROM cur INTO @txt, @name, @email

    END

CLOSE cur
DEALLOCATE cur

answered May 17, 2016 at 15:08

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using sp_send_dbmail with recieptent and content from single sql row

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related