trying to add individuals to a variable call...

  • DHeath

    SSCertifiable

    Points: 5024

    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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714080

    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: 5024

    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

    SSCertifiable

    Points: 5024

    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 '+'

  • fahey.jonathan

    Hall of Fame

    Points: 3515

    "@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: 5024

    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

     

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

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