trying to add individuals to a variable call...

Question

trying to add individuals to a variable call...

DHeath

SSCertifiable

Points: 5200
More actions
April 4, 2019 at 2:50 am

#2737191

Thanks to all that take the time to read or reply...
I have this snippet of code and i want to add a individual to the @recipients variable ...is this possible?
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile',
@recipients = @Recipients,
@copy_recipients = @CopyRecipients,
@body = @Body,
@body_format = 'HTML',
@subject = @SubjectName,
@file_attachments = @AttachmentPath;
i was trying to do this @recipients = @Recipients + 'added name', <- did not work nor did using the AND so not sure how to go about it.
This works fine IF i just leave it alone but i need to add a couple of individuals that are not on the "list" inside the @recipients variable.
Thanks Again for all replys.
Dheath
DHeath

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 1

As per: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017

This is a semicolon delimited list. If you need to add someone:

select  @recipients = @Recipients + ';added name'

DHeath SSCertifiable Points: 5200 More actions · Answer 2

Steve,

Thanks a tons and much appreciated.

Also side note.... just wanted to let you know all your hard work and time with SSC is appreciated by millions i am sure and i didnt see a place to send you some good work kudos. Thanks for everything 🙂

DHeath

DHeath SSCertifiable Points: 5200 More actions · Answer 3

made these changes and still got errors ... please assist

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Profile1',

@recipients = @Recipients + '; test_email@hp.com', --trying to add one

--@recipients = @Recipients, -- This works but i need to add one

@copy_recipients = @CopyRecipients,

@body = @Body,

@body_format = 'HTML',

@subject = @SubjectName,

@importance = 'High';

***error message below***

Msg 102, Level 15, State 1, Procedure sp_SendEmail, Line 227 [Batch Start Line 9]

Incorrect syntax near '+'

DHeath

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 4

"@Recipients + ‘; test_email@hp.com‘" is an expression, and expressions cannot be used when calling stored procedures. Any values you want must be set into variables before you call the stored procedure. You need to set your variable first, then use it in the call to the procedure.

SET @Recipients = @Recipients + ‘; test_email@hp.com‘

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘Profile1’,

@recipients = @Recipients,

@copy_recipients = @CopyRecipients, ...

DHeath SSCertifiable Points: 5200 More actions · Answer 5

Thank you very much fahey.johnathan...much appreciated as it appears that will work for me as it parsed properly with no errors. The explanation is also VERY welcomed.

DHeath