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
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.