10

I need to send emails from SQL Server, to a constantly changing list of names. Luckily, the names are updated by users into a SQL Server table, so it should always be up to date.

I have already researched, and am hoping to use sp_SQLNotify to send the emails. (This is already set up and tested with actual lists of names - NOT yet with a variable).

Anyway, I need to populate a variable with the list of email addresses to send to.

In order to do this, I need the equivalent of:

SELECT DISTINCT [Email_Username] + '@my_email_suffix.com; '
INTO @VARIABLE
FROM My_Table

EXEC sp_SQLNotify @Variable 'My Header' 'My wall of text'

Is this possible, and what is the true syntax that I need to follow?

Please forgive my ignorance, but I struggle with some of the syntax.

Thanks, Craig.

===== UPDATED WITH ANSWER =====

Thanks to Aaron.

I used your code as the basis for a working solution.

For anyone trying to do this in future, the final result is:

-- Declare variable and populate with initial apostrophe
DECLARE @var VARCHAR(MAX) = '''';

-- Populate variable with all unique email user names
SELECT @var += x.email
FROM 
(
  SELECT DISTINCT [Email_Username] + '@my_email_suffix.com;'  AS email
  FROM dbo.[My_Table]
  WHERE [Email_Username] <> ''
) AS x 

-- Remove final semi-colon
SET @var = left(@var,len(@var)-1)

-- Add final apostrophe
SET @var = @var + ''''

-- Display result
SELECT @VAR;
neminem
  • 2,658
  • 5
  • 27
  • 36
CraigA
  • 147
  • 1
  • 3
  • 13
  • possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) – squillman Feb 06 '13 at 17:25
  • Is sp_SQLNotify a built in procedure/function ? – sofl Feb 06 '13 at 17:27
  • Could you send a single email to each user in the table by looping through the table? – Nick Vaccaro Feb 06 '13 at 17:27
  • Hi, and thanks Nick. Yes, I could send an individual email to each if that would work better. sp_SQLNotify is not a built-in procedure, but is a System SP, in MASTER, as I added it to that database. What would the syntax for the loop be? – CraigA Feb 06 '13 at 17:32
  • The proposed duplicate has an accepted answer that is far more complex than it needs to be, IMHO. – Aaron Bertrand Feb 06 '13 at 17:41
  • Curious, why do you need the additional leading/trailing apostrophe? – Aaron Bertrand Feb 06 '13 at 20:20

1 Answers1

14
DECLARE @var VARCHAR(MAX) = '';

SELECT @var += x.email
FROM 
(
  SELECT DISTINCT (Email_Username + '@my_email_suffix.com;') as email
  FROM dbo.My_Table
) AS x;

SELECT @var;
neminem
  • 2,658
  • 5
  • 27
  • 36
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490