Passing the input parameter in to the @body

Question

Passing the input parameter in to the @body

ramana3327

SSCoach

Points: 19722
More actions
December 1, 2015 at 5:11 pm

#322946

Hi,
I created the SP with input parameter @job
CREATE PROCEDURE [dbo].[Sp_TestJob](@JobName SysName)
As
select job.Originating_Server Server
,job.Name JobName
,job.job_ID
,activity.run_requested_Date run_request_Date
,activity.stop_execution_date stop_execution_date
,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsedmin
FROM
msdb.dbo.sysjobs_view job
INNER JOIN -
msdb.dbo.sysjobactivity activity
ON (job.job_id = activity.job_id)
WHERE
job.name = @Job
andrun_Requested_date is not null
and stop_execution_date is null
IF EXISTS(Select
job.Originating_Server Server
,job.Name JobName
,job.job_ID
,activity.run_requested_Date run_request_Date
,activity.stop_execution_date stop_execution_date
,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsedmin
FROM
msdb.dbo.sysjobs_view job
INNER JOIN
msdb.dbo.sysjobactivity activity
ON (job.job_id = activity.job_id)
WHERE
job.name = @Job
andrun_Requested_date is not null
and stop_execution_date is null)
Begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TestProfile',
@recipients = 'ramana3327@yahoo.com',
@subject = 'Job run status',
@body = '@job is still running'
end
but here I can't use the inputparamet @job into the @body.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

You haven't defined @Job as an input parameter in the stored procedure. Please lookup CREATE PROCEDURE for more information on how to do that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 2

Assuming @job is declared a sysname, it's a string which can be concatenated:

declare @msg nvarchar(4000)

set @msg = @job + ' is already running.'

And assign @body @msg.

ramana3327 SSCoach Points: 19722 More actions · Answer 3

Hi,

Thanks. It is working some what. Here now I am getting two emails with the same msg. If I removed @body nothing is coming just email with sub is coming one time. If I keep the @body=@msg, I am getting duplicate email

I declared the input parameter for the sp @job as sysname

If the condition is true

begin

declare @msg nvarchar(4000)

set @msg = @Job + 'Job is still running'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'TestProfile',

@recipients = 'ramana3327@yahoo.com',

@subject = 'The Job Running Status',

@body = @msg

end

else

begin

set @msg=@Job + 'Job run completed'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'TestProfile',

@recipients = 'ramana3327@yahoo.com',

@subject = 'The Job Running Status',

@body = @msg

end

ramana3327 SSCoach Points: 19722 More actions · Answer 4

It is working fine.

but one doubt If we want to highlight the jobname @job near @msg is it possible to do that?

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 5

Assign @body_format 'HTML' and concatenate its tags to the @msg. I have little experience beyond that (I use text format).