Add variable to @body in msdb.dbo.sp_send_dbmail

Question

Post reply

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