Stop execution of sql script

Question

I have a huge SQL script with many batches (using GO).

On certain conditions, I want to stop the whole execution of this script.

I tried using NOEXEC, but I keep on getting invalid object errors, since my script includes creating and adding data to new tables and columns.

I do not want to use RAISERROR, is there any other way to do this?

I don't know of a way of doing this. Are the GO statements necessary? I'd look to either make it a single batch and break out of that or find some way of executing these batches individualls (e.g. Powershell or other scripting language, SSIS, put them into stored procedures etc). — Goody
– Goody, Commented Sep 24, 2015 at 13:59
How are you executing this script? Management Studio? SQLCMD? Application? — Rich Rousseau
– Rich Rousseau, Commented Sep 24, 2015 at 14:04

Giorgi Nakeuri · Accepted Answer · 2015-09-24 14:09:24Z

4

There are no good solutions to this problem. One way you can share data between batches is tables(persistent or temporary). So you can have some logic that depends on some state of that table or value of particular columns in that table like this:

--CREATE TABLE debug(col int)
--INSERT INTO debug VALUES(1)
--DELETE FROM debug


SELECT 1
GO

SELECT 2

SELECT 3

GO

IF EXISTS(SELECT * FROM debug)
    SELECT 6

GO

Just add IF EXISTS(SELECT * FROM debug) this line to every place in script that you want to skip and depending on the fact that the table has some rows or not those code blocks will execute or not.

answered Sep 24, 2015 at 14:09

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Random User Over a year ago

Seems like a good solution, but it won't make sense in my situation since I have a huge script and its not advisable to add check before every batch.

Giorgi Nakeuri Over a year ago

@RandomUser, you just have no alternative.

Jashvita · Accepted Answer · 2023-03-10 08:02:35Z

0

just use SET NOEXEC ON when you want to stop execution.

 Go
 Select 'I want to kill the job after some error or based on some validation.
 Go
 Select 'Raiserror not working'
  Go
 Select 'I have to be admin to define severity 20'
 go
Select 'I got an error, come custom validation failed, I don't want to run the 
 rest  of the script'.
Go
 SET NOEXEC ON
 Select 'rest of the script should not run after NOEXEC on executed'.

answered Mar 10, 2023 at 8:02

Jashvita

6711 gold badge12 silver badges47 bronze badges

Collectives™ on Stack Overflow

Stop execution of sql script

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related