title: How to stop execution of sql scripts when an error is encountered
body: <p>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.</p>
  
  <p>I am using sql server as my DB</p>
  
  <p>Following is the sample of my script</p>
  
  <pre><code>    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&lt;&gt;0 AND @@TRANCOUNT&gt;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&lt;&gt;0 AND @@TRANCOUNT&gt;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&gt;0 BEGIN
  PRINT 'The database update succeeded'
  COMMIT TRANSACTION
  END
  ELSE PRINT 'The database update failed'
  GO
  
  IF EXISTS (SELECT * FROM #tmpErrors) OR (@@ERROR&lt;&gt;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
  </code></pre>