How to stop execution of sql scripts when an error is encountered

Question

I have a sql file containing some sql scripts (DDL and DML) which I execute by calling from the windows command line. The issue is when an error occurs it does report the error but all the sql statements in the file are still executed, whereas i want that as soon as the first error is encountered in one of the sql statements , the execution should stop right there.

I am using sql server as my DB

Following is the sample of my script

    CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO

/** Main Scripts **/
PRINT N'Creating [dbo].[Student]'
GO
CREATE TABLE [dbo].[Student](
    [Id] [bigint] NOT NULL,
    [Subject] [varchar](15) NOT NULL,
    CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    )
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

PRINT N'Adding [StudentID] column to [dbo].[School]'
GO
ALTER TABLE [dbo].[School] ADD [StudentID] [bigint] NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

/***And many other DDL and DML statements, each followed by an error check***/


/**
 * Main teardown 
 */
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO

IF EXISTS (SELECT * FROM #tmpErrors) OR (@@ERROR<>0)
BEGIN
RAISERROR (N'An error was encountered', 20, 1 ) WITH LOG, NOWAIT, SETERROR SELECT @@ERROR AS error_number
END
GO

/**
 * Final teardown
 */
DROP TABLE #tmpErrors
GO

possible duplicate of SQL Server - stop or break execution of a SQL script — Andriy M
– Andriy M, Commented Dec 4, 2012 at 17:47

Charleh · Accepted Answer · 2012-12-04 17:20:03Z

8

I'm pretty sure BEGIN TRY and BEGIN CATCH will stop execution when an error is hit and take the execution straight to the error handler:

BEGIN TRY

   ' do stuff

END TRY
BEGIN CATCH

   'handle

END CATCH

Edit: here's an example:

BEGIN TRY

    DECLARE @int int
    SET @int = 1

    SET @int = 1 / 0

    SET @int = 2

    SELECT 'Everything OK'

END TRY
BEGIN CATCH

    SELECT 'Oops'

END CATCH

Comment out the divide by zero line above to see 'Everything OK', otherwise you will see 'Oops' in the resultset

edited Dec 4, 2012 at 17:20

answered Dec 4, 2012 at 16:48

Charleh

14k3 gold badges41 silver badges58 bronze badges

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

5 Comments

Prim Over a year ago

i tried TRY..CATCH but for some reason it doesnt go to catch. Although it does prevent the rest of the statements from executing, but i want to do the teardown step from my sample above in the catch block

Charleh Over a year ago

Try it in a new SQL query - check the TRY/CATCH, it should always move execution to the CATCH handler. Are you sure you don't have an error in your teardown code now that you've moved it all to a try/catch handler?

Prim Over a year ago

I have tried it with just PRINT N'INSIDE CATCH' RAISERROR (N'An error was encountered', 20, 1 ) WITH LOG in the catch handler

Charleh Over a year ago

Hi Priyam, are you still having trouble with this?

Prim Over a year ago

yeah, i figured catch was not being invoked because Catch is not executed for the compilation errors like table does not exist etc

Collectives™ on Stack Overflow

How to stop execution of sql scripts when an error is encountered

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related