• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2919
  • Last Modified:

Database Mail - adding a variable to the body?

Hi,

I have a table which has a trigger assigned to it so when a new record is added it sends an email to a user.

I can't seem to get the syntax right though to include my own description and the variable for the new record entry.

Here is the TRIGGER:

EXEC msdb.dbo.sp_send_dbmail
      @recipients='me@me.com',
      @body= 'A new country has been added to the system: ' + @newcountryname,
      @subject = 'New Country Added',
      @profile_name = 'Me'

The trigger doesn't work due to the + symbol, I've searched google for the correct syntax but can't figure it out.

Any help would be much appreciated.

Regards,

Ken
0
kenuk110
Asked:
kenuk110
  • 3
  • 2
1 Solution
 
apresenceCommented:
Try the attached code... You can't have string concatenation as part of an input variable for a stored procedure, so you have to declare a variable and use SET to assign it in advance.  I went ahead and variablized all of the inputs, but this is overkill.  Haven't tested the code...
DECLARE @recipients varchar(255)
DECLARE @body varchar(4000)
DECLARE @subject varchar(255)
DECLARE @profile_name varchar(255)

SET @recipients='me@me.com'
SET @body= 'A new country has been added to the system: ' + @newcountryname
SET @subject = 'New Country Added'
SET @profile_name = 'Me'

EXEC msdb.dbo.sp_send_dbmail @recipients, @body, @subject, @profile_name

Open in new window

0
 
Om PrakashCommented:
Try
declare @newcountryname  varchar(200)
SET @newcountryname = 'A new country has been added to the system: ' + @newcountryname
EXEC msdb.dbo.sp_send_dbmail
      @recipients='me@me.com',
      @body= @newcountryname,
      @subject = 'New Country Added',
      @profile_name = 'Me'
0
 
apresenceCommented:
Revised...
DECLARE @recipients varchar(255)
DECLARE @body varchar(4000)
DECLARE @subject varchar(255)
DECLARE @profile_name varchar(255)

SET @recipients='me@me.com'
SET @body= 'A new country has been added to the system: ' + @newcountryname
SET @subject = 'New Country Added'
SET @profile_name = 'Me'

EXEC msdb.dbo.sp_send_dbmail @recipients=@recipients, @body=@body, @subject=@subject, @profile_name=@profile_name

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
kenuk110Author Commented:
apresence:

Perfect, brilliant, worked a treat!

As an aside, how would I place a query in to the Body, say the rest of the table entries, for instance:

DECLARE @Query

@query=’SELECT countryCode, countryName FROM country ’

So it would say United Kingdom was added...

Updated entries:

United States
United Kingdom

No problem if yo don't know, I'll close this and ask again, just thought I'd be cheeky and ask.

Regards,

Ken

0
 
apresenceCommented:
Well, by your own admission you're being cheeky ;).  If you ask that as a new question, I'm sure someone would be happy to answer.
0
 
kenuk110Author Commented:
Perfect, thank you for this!

Best Regards,

Ken
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now