When an MSSQL stored procedure fails, the error is not shown in the Automic job report/log.
You can add a 'print' inside the stored procedure, or in the Automic SQL job itself. However, 'print' will not be processed if this is after a statement that failed.
select * From invalid table -- this fails
In both cases, it will print out 'start', but 'print' after the failing statement will not be processed. Job Report will only show 'start'.
You can try something like this on the SQL Job to capture the error, and have the error message display in the Job Report. Once you have the error in the Job Report, you can handle the error in 'Post Process'.
declare @sql varchar(100) = 'sp_test'
declare @err int
set @err = @@ERROR
if @err <> 0
print N'ERROR' + str(@err)