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')
@RaiseError("Job Failed",11,1)
end
else if(@flag='R')
@RaiseError("Running",11,1)
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