Stored procedure using sp_send_dbmail to send emails to multiple recipients queried from database

Question

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.

The address table is pretty simple, looks something like this, but much larger. Both columns are varchars:

idNumber   |  email
__________________________
   a123    |  [email protected]
   a123    |  [email protected]
   1-100   |  [email protected]

So if we're sending to ID number "a123", an email needs to go to both Steve and Carol.

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.

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;

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?

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?

I appreciate your input. Thank you.

EDIT: Here's Kritner's working solution! Thanks a bunch!

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

Solution

You can do a query and assign the values to a variable as such:

DECLARE @myRecipientList varchar(max)
SET @myRecipientList = (STUFF((SELECT ';' + emailaddress FROM table FOR XML PATH('')),1,1,''))

This will set your @myRecipientLIst to a ";" delimited list of the recipients specified your query.

You could also do the same sort of idea with a SP, just throw them into a temp/variable table and stuff into a semi colon separated list.

EDIT:

Finally to send the mail you could do:

EXEC msdb.dbo.sp_send_dbmail
  @recipients = @recipientList,
  @subject = @subject,
  @body = @body // ........

COMMENT EDIT:

based on your original query, your stuff query should look something like this:

DECLARE @myRecipientList varchar(max)
SET @myRecipientList = STUFF((SELECT ';' + email FROM emailTable WHERE idNumber = @idNumber FOR XML PATH('')),1,1,'')

The idea behind this is - for every email found in the email table append to @myrecipientList the email found and a semi colon.