February 3, 2011 at 6:21 pm
Is there a way for me to have more than one @body parameter in my msdb.dbo.sp_send_dbmail statement.
What I would like to do is have multiple @body parameters. So, that I can pass a value to a selected @body parameter. Below is an example of what I am trying to do.
OPEN csrEmails
FETCH NEXT FROM csrEmails
INTO @ApplicantEmail, @FirstName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMailAccount',
@recipients = @ApplicantEmail,
@subject = Company new Employee Information',
@body = '
Dear @FirstName, <--Would like to have more than one @body parm
so I can pass values to selected @body parms
@body1 = 'Other texted here'
@body2 = @Job Title
Congratulations on your recent hiring by our company! As a new employee, you will receive the following important items:
<li>A Employee ID number</li>
<li>A email address, e.g. first.last@mycompany</li>
<li>A username and password that you will use to access many online resources</li>
<li>A district extension </li>
To retrieve this information online, just enter the email address that you used when you completed your online application, along with your first and last name and the last 4 digits of your phone number.',
@body_format = 'HTML'
FETCH NEXT FROm csrEmails
INTO @ApplicantEmail, @FirstName
END
February 4, 2011 at 5:15 am
Not quite sure what you're looking for. Maybe create a variable called @Body_Temp and build that as you need, then SET @BODY = @BODY_TEMP when you have it built the way you want.
February 7, 2011 at 7:19 am
I would use the dreaded Dynamic SQL to build your email. Embed the whole proc and arguments into the dynamic sql, adding in the proper name, then execute it.
Depending on where you're getting your name values from, and how many you're passing at a time, this may require some looping or a CTE. If you're only triggering it once, though, then you can just pass the name via variable into the dynamic SQL and you'll be set.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply