title: Stored procedure using sp_send_dbmail to send emails to multiple recipients queried from database
body: <p>Like the title says, I'm trying to make a stored procedure used to send emails to addresses stored in the database, often including multiple recipients. I've tried a few approaches to this, but I think I'm hitting a wall and would appreciate any input anyone has. The main issue I've had is querying the database for the addresses and passing that to sp_send_dbmail in a way that works.</p>
  
  <p>The address table is pretty simple, looks something like this, but much larger. Both columns are varchars:</p>
  
  <pre><code>idNumber   |  email
  __________________________
     a123    |  steve@test.com
     a123    |  carol@test.com
     1-100   |  mary@test.com
  </code></pre>
  
  <p>So if we're sending to ID number "a123", an email needs to go to both Steve and Carol.</p>
  
  <p>Here's a super simple procedure. It's not verbatim since this is all on my work computer, but more of a skeletal gist of what I'm going after.</p>
  
  <pre><code>CREATE PROCEDURE sendMail
    @idNumber varchar(MAX),
    @subject varchar(MAX),
    @body varchar(MAX)
  EXEC msdb.dbo.sp_send_dbmail
    @recipients = "EXEC SELECT email FROM emailTable WHERE idNumber = " + @idNumber + "';",
    @subject = @subject,
    @body = @body;
  </code></pre>
  
  <p>It throws and error; it doesn't seem to like concatenating the ID parameter into the query. I tried making a separate procedure to query emails, but passing the ID parameter to the procedure didn't seem to work either. Even if I did successfully pass the parameter and get the query to execute successfully, I'd still need to join the two results in a single string and delimit them with semicolons so they'll play nice with sp_send_dbmail. I think?</p>
  
  <p>SQL wizards, how would you approach this? I'm not wildly experienced, so is there something simple and syntactic I'm doing wrong? Is my approach flawed fundamentally?</p>
  
  <p>I appreciate your input. Thank you.</p>
  
  <p><strong><em>EDIT:</em></strong> Here's Kritner's working solution! Thanks a bunch!</p>
  
  <pre><code>CREATE PROCEDURE testMail2
  @idNumber varchar(MAX)
  AS
  BEGIN
  DECLARE @recipientList varchar(MAX)
  SET @recipientList = (STUFF((SELECT ';' + email FROM emailTable WHERE idNumber = @idNumber FOR XML PATH(' ')),1,1,''))
  EXEC msdb..sp_send_dbmail
  @recipients=@recipientList,
  @subject='Subject Line',
  @body='Body Text'
  END
  </code></pre>