Geek Answers Handbook

How to stop sql script execution when an error occurs

I have a sql file containing some sql scripts (DDL and DML) that I execute by calling from a Windows command prompt. The problem is that when an error occurs, it reports an error, but all the sql statements in the file are still executing, whereas I want the first error to occur in one of the sql statements, the execution should stop right there.

I use sql server as my database

Below is a 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
+4
source share
1 answer

I am sure that BEGIN TRY and BEGIN CATCH will stop execution when the error is removed, and execute the execution directly in the error handler:

 BEGIN TRY ' do stuff END TRY BEGIN CATCH 'handle END CATCH

Edit: here is 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 on the separation by the zero line above to see “Everything is fine”, otherwise you will see “Oops” in the result set

+5
source

More articles:


All Articles