Stop Agent Job from Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stop Agent Job from Query

Author

Topic

Johnph
Posting Yak Master

103 Posts

Posted - 2012-09-12 : 09:04:06

Hello,

I have a bit of a complex question.
I have a query that uses an IF ELSE clause similar to this:

IF EXIST (SELECT * FROM TABLE1)

INSERT INTO TABLE2 (A , B)
VALUES (1, 1)

ELSE

----query to stop agent job----

My question is if I do

IF EXIST (SELECT * FROM TABLE1)

and no data exist, it will resort to the ELSE clause correct?

My second question is if there is a way to execute a query that will stop the agent job or possible tell it to skip to the next step if it doesn't find data from

IF EXIST (SELECT * FROM TABLE1)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-12 : 09:30:23

Yes, it will skip the insert statement and execute whatever is in the else clause. The thing to be careful about though, is that, if you have more than one statement in either block, be sure to wrap them in a BEGIN and END - for example:

IF EXISTS (SELECT * FROM TABLE1)
BEGIN
	INSERT INTO TABLE2 (A , B)
	VALUES (1, 1)
	
	INSERT INTO TABELE3 (X,Y) VALUES (7,8)
END
ELSE
BEGIN
	INSERT INTO TABELE3 (X,Y) VALUES (17,28)
	INSERT INTO TABELE4 (X,Y) VALUES (17,38)
	INSERT INTO TABELE5 (X,Y) VALUES (17,48)
END

To your second question sp_stop_job can stop a job. http://msdn.microsoft.com/en-us/library/ms182793.aspx

Johnph
Posting Yak Master

103 Posts

Posted - 2012-09-12 : 10:10:58

Hey sunitabeck, thanks a ton. Is there a way to say skip to next step within a Job?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 11:12:41

quote:
Originally posted by Johnph

Hey sunitabeck, thanks a ton. Is there a way to say skip to next step within a Job?

isnt it enough to include the check inside a step and execute the rest of query only if it satifies

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Johnph
Posting Yak Master

103 Posts

Posted - 2012-09-12 : 12:33:45

Yeah, I decided to create a new jobs and then call it if it satifies of clause. Thanks to both.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 12:56:40

ok...cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources