SQL Server: How to abort a series of batches in Query Analyzer?

Question

i have a series of T-SQL statements separated by the special Query Analyzer batch separator keyword:

GO

If one batch fails, i need Query Analyzer to not try subsequent batches - i want it to stop processing the series of batches.

For example:

PRINT 'This runs'
go

SELECT 0/0, 'This causes an error'
go

PRINT 'This should not run'
go

Output:

This runs
Server: Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
This should not run

Possible?

---

Update

An example of this in real use might be:

 sp_rename 'Shelby', 'Kirsten'
 go

 DROP VIEW PeekAView
 go

 CREATE VIEW PeekAViewAS 
 SELECT * FROM Kirsten
 go

Answer 1

Create a temporary table; and update it after each step (if successful); and then check the success of the previous step by validating against the table.

create table #ScriptChecker (SuccessfullStep int)

-- Do Step One
Insert into #ScriptChecker
Select 1

-- Step 2
If exists (select * from #ScriptChecker where SuccessfullStep = 1)
-- Do Step 2 ...

Answer 2

based on @u07ch idea, but only insert on failure...

create table #test (failure  int)

if not exists (select * from #test)
BEGIN
    print 'one' --sql here
END
go

if not exists (select * from #test)
BEGIN
    print 'two'--sql here
END
go

if not exists (select * from #test)
BEGIN
    print 'three' ---SQL SERVER 2000 version
    --error--
    SELECT 0/0, 'This causes an error'
    IF @@ERROR!=0
    BEGIN
        insert into #test values (1)
        PRINT 'ERROR'
    END
end 
go

if not exists (select * from #test)
BEGIN
    print 'three'  ---SQL SERVER 2005/2008 version
    BEGIN TRY
    --error--
        SELECT 0/0, 'This causes an error'
    END TRY
    BEGIN CATCH
        insert into #test values (1)
        PRINT 'ERROR'
    END CATCH
END
go

if not exists (select * from #test)
BEGIN
    --sql here
    print 'four'
END
go

output 2000:

one
two
three

----------- --------------------
Msg 8134, Level 16, State 1, Line 7
Divide by zero error encountered.

(1 row(s) affected)

ERROR

output 2005/2008:

one
two
three

----------- --------------------

(0 row(s) affected)

(1 row(s) affected)

ERROR

Answer 3

Erland Sommarskog in the microsoft.public.sqlserver.programming group had a very good idea:

In a change script such as the one you posted, you need to be defensive, and start each batch with IF @@trancount > 0.

Using

IF @@trancount > 0

is much cleaner.

Answer 4

You can activate the "Query, SQLCMD Mode" menu option and place the following at the beginning of the script:

:on error exit

This will stop execution when an error occurs, even if there are subsequent batches.

Just make sure that you don't accidentally run the script without SQLCMD mode on because you will get the typical behavior where errors are ignored.

Answer 5

When I need to do this, I issue a RAISERROR of severity 20. This, or higher, will kill the current connection, and prevent subsequent "GO batches" from executing. Yes, it can be awkward, but it does the job.