[SQL] Executing SQL Server Agent Job from a stored procedure and returning job result

Need to have a stored procedure that calls a SQL Server Agent Job and returns whether or not the job ran successfully or not.

So far I have

CREATE PROCEDURE MonthlyData
AS
EXEC msdb.dbo.sp_start_job N'MonthlyData'

WAITFOR DELAY '000:04:00'

EXEC msdb.dbo.sp_help_jobhistory @job_name = 'MonthlyData'
GO


Which starts the job, whats the best way to get back if the job ran successfully or not?

Ok made an edit and used WAITFOR DELAY as the job normally runs between 3-4 mins never longer than 4. Does the job but is there a more efficient way to do it?

Continue reading...