SQL Server 2016作业通用脚本需求：条件触发步骤或成功退出

Got you covered with a script that avoids hardcoding job or step names, while handling your conditional logic perfectly. Here's how to set it up:

Step 1: Configure the Job Step Properties #

First, tweak your Job Step 1 settings: set its On success action to Quit the job reporting success. This ensures that if the T1 table has no number=1 records, the job exits cleanly right after Step 1.

Step 2: The Generic Script for Step 1 #

Paste this script into Step 1's command window. It dynamically grabs the current job and step details, checks for your target record, and triggers Step 2 only when the condition is met:

DECLARE @CurrentJobID UNIQUEIDENTIFIER,
        @CurrentStepID INT,
        @CurrentJobName SYSNAME,
        @NextStepName SYSNAME;

-- Fetch details for the currently running job and step
SELECT 
    @CurrentJobID = ja.job_id,
    @CurrentStepID = ja.last_executed_step_id
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobsteps js 
    ON ja.job_id = js.job_id 
    AND ja.last_executed_step_id = js.step_id
WHERE 
    ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
    AND ja.stop_execution_date IS NULL
    AND ja.job_id IS NOT NULL;

-- Get the name of the current job
SELECT @CurrentJobName = name 
FROM msdb.dbo.sysjobs 
WHERE job_id = @CurrentJobID;

-- Get the name of the next step (assumes Step 2 is the immediate follow-up with step_id = current +1)
SELECT @NextStepName = step_name 
FROM msdb.dbo.sysjobsteps 
WHERE job_id = @CurrentJobID 
    AND step_id = @CurrentStepID + 1;

-- Check if T1 has a record where number = 1
IF EXISTS(SELECT 1 FROM T1 WHERE number = 1)
BEGIN
    -- Trigger the next step dynamically without hardcoding
    EXEC msdb.dbo.sp_start_job
        @job_name = @CurrentJobName,
        @step_name = @NextStepName;
END
-- No ELSE block needed: the job will exit successfully via the step's pre-configured success action

Key Details to Note #

• No Hardcoding Required: The script pulls all job/step metadata directly from SQL Server's system views, so it works for any job where Step 2 is the immediate next step. If your step numbering follows a different pattern, just adjust the step_id = @CurrentStepID +1 logic to match your setup.
• Clean Job Flow: By setting Step 1's success action to quit, we ensure the job stops immediately when the condition isn't met. When the condition is satisfied, sp_start_job kicks off Step 2 without relying on the job's default step progression.
• SQL Server 2016 Compatibility: All system views and stored procedures used here are fully supported in SQL Server 2016.

内容的提问来源于stack exchange，提问作者Mund