Add variable to @body in msdb.dbo.sp_send_dbmail

Question

Add variable to @body in msdb.dbo.sp_send_dbmail

igngua

SSCrazy

Points: 2510
More actions
December 16, 2010 at 1:24 pm

#230886

Hi !
I´m setting up a trigger to execute msdb.dbo.sp_send_dbmail.
The mailing is working also the trigger but i need to add the selected values from inserted into the @body variable.
alter TRIGGER enviacorreos on infarchivo
for insert
AS
declare @infanlab int
declare @infanmuestra char(13)
declare @infaobs varchar (700)
declare @infaversion smallint
IF exists (select * from inserted i where i.infaversion >1)
begin
select @infanlab = i.infanlab from inserted i
select @infanmuestra = i.infanmuestra from inserted i
select @infaobs = i.infaobs from inserted i
select @infaversion = i.infaversion from inserted i
exec msdb.dbo.sp_send_dbmail @profile_name = 'prueba',
@recipients ='test@test.com',
@subject ='Nueva Versión de Informe',
@body = 'Ha sido generada una nueva versión para el informe en PDF identificado como:
NºLab: @infanlab
Muestra: @infanmuestra
Versión: @infaversion
Mensaje enviado automaticamente'
end
I need to add @infanlab, @infanmuestra, @infaversion to the @body with the displayed text.
any ideas??
thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

it is actually deceptively simple...you just repalce a palceholder in the msg body with the variable actuial value ...just make sure the variable values cannot be null, and you should be all set:

...

declare @body varchar(max)

@mybody = 'Ha sido generada una nueva versión para el informe en PDF identificado como:

NºLab: @infanlab

Muestra: @infanmuestra

Versión: @infaversion

Mensaje enviado automaticamente'

--replace the placeholders of the variable name with the variable value.

SET @mybody = REPLACE(@mybody,'@infanlab',@infanlab)

SET @mybody = REPLACE(@mybody,'@infanmuestra',@infanmuestra)

SET @mybody = REPLACE(@mybody,'@infaversion',@infaversion)

exec msdb.dbo.sp_send_dbmail @profile_name = 'prueba',

@recipients ='test@test.com',

@subject ='Nueva Versión de Informe',

@body = @mybody

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

upstart Ten Centuries Points: 1255 More actions · Answer 2

Try creating another variable for the body, then forming a string that includes the variables you need:

Declare @Body VARCHAR(500)

Set @Body = "This is a test:' + @variable + ' More of the message with another variable:' + @variable2

Then, in your sp_send_dbmail you would just use the @Body, which would include your text and variables:

EXEC msdb.dbo.sp_send_dbmail

@Recipients = ''

, @Subject =''

, @Body = @Body

igngua SSCrazy Points: 2510 More actions · Answer 3

Thanks lowell!! i was missing the replace function!!

i got this

alter TRIGGER enviacorreos on infarchivo

for insert

AS

declare @infanlab int

declare @infanmuestra char(13)

declare @infaobs varchar (700)

declare @infaversion smallint

declare @mybody varchar (max)

declare @myinforme nvarchar (255)

IF exists (select * from inserted i where i.infaversion >1)

begin

select @infanlab = i.infanlab from inserted i

select @infanmuestra = i.infanmuestra from inserted i

select @infaobs = i.infaobs from inserted i

select @infaversion = i.infaversion from inserted i

set @myinforme = 'Nueva Versión de Informe: @infanlab'

set @myinforme = replace(@myinforme,'@infanlab',@infanlab)

set @mybody = 'Ha sido generada una nueva versión para el informe en PDF identificado como:

NºLab : @infanlab

Muestra: @infanmuestra

Versión: @infaversion

Motivo : @infaobs

Mensaje enviado automaticamente por el sistema LABMANAGER de Hidrolab S.A., Por favor no responder.'

SET @mybody = REPLACE(@mybody,'@infanlab',@infanlab)

SET @mybody = REPLACE(@mybody,'@infanmuestra',@infanmuestra)

SET @mybody = REPLACE(@mybody,'@infaversion',@infaversion)

set @mybody = replace(@mybody,'@infaobs',@infaobs)

exec msdb.dbo.sp_send_dbmail @profile_name = 'prueba',

@recipients = test@test,

@subject = @myinforme,

@body = @mybody

end