Skip to content

Instantly share code, notes, and snippets.

@shaneis
Last active October 11, 2022 21:20
Show Gist options
  • Save shaneis/476c8141b9d4de2afa74ef388738828b to your computer and use it in GitHub Desktop.
Save shaneis/476c8141b9d4de2afa74ef388738828b to your computer and use it in GitHub Desktop.
Dynamic SQL T-SQL Tuesday 155.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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment