遇到错误时如何停止执行sql脚本

Question

我有一个包含一些sql脚本(DDL和DML)的sql文件，这些脚本是通过从windows命令行调用来执行的。问题是，当发生错误时，它会报告错误，但文件中的所有sql语句仍然会执行，而我希望在其中一个sql语句中遇到第一个错误时，执行应该立即停止。

我使用sql server作为我的数据库

以下是我的脚本示例

    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

Answer 1

我非常确定当命中错误时，BEGIN TRY和BEGIN CATCH将停止执行，并将执行直接转到错误处理程序：

BEGIN TRY

   ' do stuff

END TRY
BEGIN CATCH

   'handle

END CATCH

编辑:下面是一个例子：

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

注释掉上面的除以零行以查看“一切正常”，否则您将在结果集中看到“Oops”