CODEWITHSUNDEEP
sqlsql-server-2008
hhhh
hhhh

Reputation: 11

SQL mail ------DATABASE MAIL

Dear all, I am using SQL Server 2008.

I am facing a scenario where I have to send mail to single or multiple user depending on the query. I tried this stmt to send mail to multiple recipients

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'imran' , 
@recipients= '[email protected];[email protected]', 
@subject = 'Test mail'

It succesfully sent mail.

Now I want to send mail depending on query. With single employee it is no issue, but if it is more then one employee how can I mail to multiple recipients.

That is my question is in this sp to send multiple recipients one has to separate addresses with ; . How can I arrange recipients so that the ; comes in between.

Thank you.

Upvotes: 1

Views: 3157

Answers (3)

Amrita Srivastava
Amrita Srivastava

Reputation: 384

for the recipients, you can seggregate the miultiple email id's by [];

declare  @ccmailid varchar(2000);

select @ccmailid = stuff((select '],['+ mailid  from table_name
                    order by '],['+ mailid
                    for xml path('')),1,2,'')+']'

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'imran' , 
@recipients=  @ccmailid, 
@subject = 'Test mail'

Upvotes: 0

marc_s
marc_s

Reputation: 754478

Unfortunately, you're not giving us a lot to go on - you don't show us the query to select your employees, you're not telling us anything about what the Employee table looks like, so all we can do is guessing - at best.

So here's my guess: you want to somehow select multiple employees, and their e-mail addresses should be concatenated together, separated by a semicolon (;), and then used for your sp_send_dbmail call.

One method to do this could be this:

DECLARE @recipients VARCHAR(4000)

SELECT 
   @recipients = STUFF((SELECT ';' + EMail 
                        FROM dbo.Employees
                        WHERE (some condition here to find the right employees)
                        FOR XML PATH('')
                       ), 1, 1, '')

EXEC msdb.dbo.sp_send_dbmail 
        @profile_name = 'imran', 
        @recipients, 
        @subject = 'Test mail'

Upvotes: 2

pavanred
pavanred

Reputation: 13803

Use a function like this - 

CREATE FUNCTION coltocsv
(
    --your input parameters 
)
RETURNS nvarchar(3000)
AS
BEGIN
      -- Declare the return variable here
declare @keywords nvarchar(3000) 

--Build your csv string of keywords
Select @keywords = null
SELECT @Keywords = Coalesce(@Keywords + '; ', '') +
ColumnName
from Table
where <some condition>

--- Return the result of the function
      RETURN @keywords
END

Upvotes: 0

Related Questions