How to quit a script

[Glasgow]

I have a large script to run in Query Analyser containing multiple batches (so GOTO no good) and want a clean way of exiting if a return code from a stored procedure is a cretain value.

How can I quit the script without executing remaining statements and preferably without triggering a write to the system log (which I understand RAISERROR would do in some circumstances).

Thanks in advance.

 

[ClaireHCM]

You can create a job which has several steps ,In the steps Tab you can put the execute the batch one by one ,in the step Tab there are 2 sub-tab Advanced and General ,in Advanced tab you can set up what should do if "On failure action" to Quit the job reporting failure.

 

[Glasgow]

How do I activate this Steps tab? Is it available in Query Analyser?

 

[ClaireHCM]

You should create a job in Enterprise manager and using T-SQL.
The steps of setting a job will be
Open Enterprise Manager --- Expand Management -- SQL Server Agent --- Right click jobs --- New Jobs ---You will see 4 tabs General,Steps,Schedules and Nitification.
You should first name the job in General tab then click on Steps tab to set up steps of job(click on new for each step you want to execute)

You can also use T-SQL ,but it's much more complicated than using Enterprise MAnager,check sp_add_job and it's related stored procedure to query!

 

[Glasgow]

Thanks for the suggestion. My preferred route would be via T-SQL but presumably then I have to put all my code into the step's @command parameter i.e. reams of T-SQL within a text string which is often such a pain to read and loses all the syntax checking and colour coding that Query Analyser offers.

I think I may choose to live with RAISERROR and appropriate severity level but thank you again.

 

[LokiDba]

TO stop the script just use the RETURN statment eg

DECLARE @li_Return int

EXECUTE sp_SomeThing @li_Return OUTPUT

IF @li_Return <> 0
BEGIN

RETURN -- This will stop the script

END

Hope this is of use.

Andy (LokiDBA)

 

[Glasgow]

Thanks Andy but I had already tried this and it does not appear to work - it works within a stored procedure but not within a script being run from Query Analyser.

 

[ClaireHCM]

Do u have permission issue?Why u need to choose Raiserror to do it?
sp_add_job cant do the job?

 

[Glasgow]

Andy - to clarify, RETURN will exit the batch but my query has multiple batches.

ClaireHsu - I am concerned that, in order to use sp_add_job (and presumably sp_add_jobstep?), I will have to embed all the T-SQL required in string parameters (@command?) which will significantly decrease the legibility of the script.