SQL Server Job

Question

Hi I am creating a sql server job

in job step i am calling a store procedure.

This step should retry 50 times at interval of 3 minutes if status is R. after 50 retries if status is not Y it will failed
If status is N it should fail immediately. now this status N can become at any time. it can become at first retry or 50th retry.
If Status is Y it should be success.

Following is procedure for reference

Create procedure sp_test

as

begin

decalre @flag char(1)

set @flag = (Select status from tbl1)

if(@flag = 'N')

begin

    @RaiseError("Job Failed",11,1)

end

else if(@flag='R')

begin

  @RaiseError("Running",11,1)

end

else if (@flag='Y')
begin

    print 'Success'

end
end

this sp_Test procedure i am calling from job step

now as we can see it is reading status from tbl
what is occuring right now
first try flag is R it will raise error , start second try. if it till 50 retries flag is R it will fail. this is correct
now if at any retry till 50 flag becomes Y it will succeed
the scenario which is failing is for N
for N also it will retry till all 50 tries and then it will fail
i want to stop the execution of job and give fail message as soon as status become N

how to do this. please let me know

i have tried using sp_Stop job which is in msdb db,also increase severity of raiseerror,also tried writing select 1/0 for status N. it is not working.
the ask is retries should stop immediately when status is N and report failure

Answer 1

Throw is not working

Answer 2

CREATE PROCEDURE sp_test
AS
BEGIN
    DECLARE @flag CHAR(1);
    
    SET @flag = (SELECT status FROM tbl1);

    IF (@flag = 'N')
    BEGIN
        -- Critical error for immediate failure
        THROW 50001, 'Job Failed due to status N', 16;
    END
    ELSE IF (@flag = 'R')
    BEGIN
        -- Error to trigger a retry
        THROW 50002, 'Retry: Status is still R', 11;
    END
    ELSE IF (@flag = 'Y')
    BEGIN
        PRINT 'Success';
    END
END

Try the above procedure and nn the job step settings, set the “On failure” action to "Quit the job reporting failure." This will ensure that the job stops immediately if status N is encountered.