CODEWITHSUNDEEP
sqlsql-server
Prim
Prim

Reputation: 1364

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

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

Upvotes: 8

Views: 12434

Answers (1)

Charleh
Charleh

Reputation: 14012

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

Upvotes: 8

Related Questions