CODEWITHSUNDEEP
sqlsql-servert-sqlemail
User1899289003
User1899289003

Reputation: 872

Send Email to each recipient stored into a table

I'm trying to send an email using the sql server email option, I already can send an email, but now I'm trying to send that email to dynamic recipients, for example... I have the following table:

ID | Email
..1| [email protected]
..2| [email protected]

With my code I need to do the following thing:

@recipients = '[email protected]; [email protected];'

What I want to do is to select that emails from a table, so if I edit one email I dont have to change anything about my code, just update the information from my table.

I'm thinking maybe do it by using a var, something like:

Declare @emails nvarchar(max)
set @email = query
then do something like a foreach (foreach email in @emails) or something like that (in T-SQL of course...)

Is this possible? Or what another solution can I try?

Upvotes: 0

Views: 775

Answers (3)

Francisco Fernandez
Francisco Fernandez

Reputation: 841

Recipients has the option to add more than one email in the same line, so maybe the easier way to solve your problem is select all the emails and then join them separated with a ;. If you thing that this is a good option you should try this code:

Declare @Emails nvarchar(MAX)

Select @Emails = coalesce(@Emails + ';', '') + Email from yourTable
//Your output should be: [email protected];[email protected]

Then all you need to do this:

@recipients = @Emails

Upvotes: 1

asemprini87
asemprini87

Reputation: 347

You need to concatenate the strings in the column, so something like this should work: 

CREATE TABLE dbo.exampleTable (
    id int,
    email varchar(200)
    )

INSERT INTO dbo.exampleTable VALUES (1,'a@a'),(2,'b@b')

SELECT stuff( (SELECT ';'+email
               FROM dbo.exampleTable 
               FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
            ,1,1,'')

There are also some other ways to do it: https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

The result of the query you build or use can be used to set your @email variable.

Upvotes: 0

jcaliz
jcaliz

Reputation: 4021

You should use a cursor for that

DECLARE @email nvarchar(max)
set @email = ''
DECLARE @cursorName CURSOR FOR
SELECT emailColumn FROM tableName
OPEN @cursorName

Then you use a loop to concatenate each email

FETCH NEXT FROM @cursorName INTO @email   

WHILE @@FETCH_STATUS = 0   
BEGIN   
   set @email = @email+@cursorName+'; '
END

Upvotes: 0

Related Questions