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
kenuk110Asked:
Who is Participating?
 
apresenceConnect With a Mentor Commented:
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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
All Courses

From novice to tech pro — start learning today.