Dynamic SQL T-SQL Tuesday 155.sql

JobStatus.sql

DECLARE
	@job_sql AS nvarchar(max),
	@job_name AS nvarchar(128) = N'syspolicy_purge_history', -- = N'No existy',
	@debug_mode_on AS bit = 1; -- 0;

DECLARE
	@nl AS nchar(2),
	@actual_job_name AS nvarchar(128);

/* Newline for formatting */
SET @nl = NCHAR(13) + NCHAR(10);

/* Job existence check */
IF @job_name IS NOT NULL
BEGIN
	SET @actual_job_name = (
		SELECT
			[name]
		FROM msdb.dbo.sysjobs
		WHERE
			[name] = @job_name
	);

	IF @actual_job_name IS NULL
	BEGIN
		DECLARE @err_msg AS nvarchar(max);

		SET @err_msg = FORMATMESSAGE(N'Cannot find any job labelled: %s', @job_name);
		RAISERROR(N'%s', 0, 1, @err_msg) WITH NOWAIT;
		RETURN
	END;
END;

SET @job_sql = N'USE msdb;
SELECT TOP (1)
	is_running = CASE
		WHEN ja.job_id is NOT NULL AND ja.stop_execution_date IS NULL
			THEN 1
		ELSE 0
	END,
	last_run_time = ja.start_execution_date,
	next_run_time = ja.next_scheduled_run_date,
	last_job_step = js.step_name,
	job_outcome = CASE
		WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL
			THEN N''Running''
		WHEN jh.run_status = 0
			THEN N''Failed''
		WHEN jh.run_status = 1
			THEN N''Succeeded''
		WHEN jh.run_status = 2
			THEN N''Retry''
		WHEN jh.run_status = 3
			THEN N''Cancelled''
	END
FROM dbo.sysjobs AS j
LEFT JOIN dbo.sysjobactivity AS ja
	ON ja.job_id = j.job_id
	AND ja.run_requested_date IS NOT NULL
	AND ja.start_execution_date IS NOT NULL
LEFT JOIN dbo.sysjobsteps AS js
	ON jh.job_id = js.job_id
	AND js.step_id = ja.last_executed_step_id
LEFT JOIN dbo.sysjobhistory AS jh
	ON jh.job_id AND j.job_id
	AND jh.instance_id = ja.job_history_id;'

/* Add filter: job_name */
IF @actual_job_name IS NOT NULL SET @job_sql = CONCAT(
	@job_sql, @nl,
N'WHERE
	j.[name] = @ds_job_name'
);

/* Add sorting */
SET @job_sql = CONCAT(
	@job_sql, @nl,
N'ORDER BY
	ja.start_execution_date DESC;
	'
);

IF @debug_mode_on = 1
BEGIN
	RAISERROR(N'%s%s', 0, 1, @job_sql, @nl) WITH NOWAIT;
END;
ELSE
BEGIN
	EXECUTE [master].[sys].sp_executesql
		@stmt = @job_sql,
		@param1 = N'@ds_job_name AS nvarchar(128)',
		@ds_job_name = @actual_job_name;
END;