Stop job from stored proc

[jmj]

I have a job that has several steps. It imports data from another database. Using a stored procedure, it then deletes the data from the current database and replaces it with the imported data. From there it goes on to run another series of stored procedures that update the data.
Problem: Sometimes the imported tables are blank (due to an error with that database).
If these tables are empty I want the job to stop there.
I know I can do an if/else statement in my stored proc that does the delete old data/insert new data part. (If count(*)>=1 delete data....). But in doing that the other steps of the job would still run. (not a big deal)
But I'm wondering about a more efficient way. Can I make
a step in the job that looks at the number of records in these tables. If the tables have a count of 0 then the job fails and all other steps do not run.
Thanks,
j

 

[AtomicWedgie]

Hi,

something like this:

declare @count int

set @count = 0

select @count = count(*)
from <your imported table>

IF (@count = 0) RETURN

Regards,

Atomic Wedgie

 

[hmckillop]

If you do a raiserror and you have in your job step, on error quit with failure then it should terminate
e.g

declare @count int

  set @count = 0

  select @count = count(*)
  from <your imported table>

  IF (@count = 0)
 BEGIN
  RAISERROR ('The job needs to terminate.',16, 1)

  RETURN 
END

"I'm living so far beyond my income that we may almost be said to be living apart