Stop Job Based on SP Return Value?

[smedvid]

I need to stop a Job from ever starting if certain conditions are not met. So Step 1, would call a procedure (or DTS?) that would return a boolean (T/F) to continue the job. I have the SP created, that simply does a Select against a table. So... what is my next step? I've never returned values from an SP, although I have used the DTSRun flag before to abort a DTS Package? I was leaning to DTS since I know how that can work... Can I simply call the SP directly though? tia..

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[viper1777]

Hi,
Here is some code to create a proc that will return an integer. In this eg it queries the top id field in my_table and if it equals 1 then it returns a 1, else it returns 0.

create proc sp_test
as
declare @id int

select top 1 @id=[id] from my_table

--print @id --debug code

if @id = 1
 return(1)
else
 return(0)

and to run it...

declare @res int
exec @res=sp_test
print @res

If you need help with disabling a job check out the stored procs in the msdb database, they all contain the word "job".

Thanx!

Dave Shaw
Nothing is Impossible, it is just something I haven't got round to doing. - Me
History admires the wise, but elevates the brave. - Edmund Morris

 

[mrdenny]

To stop a job use the procedure sp_stop_job.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[smedvid]

thanks.. sp_stop_job may be the key...

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...