There're several scheduled SQL Agent jobs in my 2019 Server. Each job-One Stored Procedure which I usually call a container consists of several Stored Procedures similar to below.
SQL_Agent_Job_1
Create Procedure SQL_Agent_Job_1 as
BEGIN
exec Stored_proc1
exec Stored_proc2
exec Stored_proc3
exec Stored_proc4
END
SQL_Agent_Job_2
Create Procedure SQL_Agent_Job_2 as
BEGIN
exec Stored_proc5
exec Stored_proc6
exec Stored_proc7
exec Stored_proc8
END
I need the job to fail if it runs into an error at either of the steps immediately, say for instance if Job 1 runs into a data overflow error in StoredProc2, I'd want the job 1 to fail without executing StoredProc3 and 4. This is the case in one of my jobs but isn't in a few.
So job 2, even if there's a invalid column name error in StoredProc6 and wasn't able to complete all the actions it's supposed to such as inserting some rows to a table, will ignore that move on to StoredProc7 and 8 and afterwards generates an error with job 2 failed. this makes it really difficult to track at which point exactly the job failed.
I'd like to understand what's causing this behavior and what changes should be made in order for a job to fail instantly when an error is encountered.