SQL Server批处理运行时错误的影响

 

前言：批处理是同时从应用程序发送到 SQL Server 2005 并得以执行的一组单条或多条 Transact-SQL 语句。我们通常认为当一个批处理的多条语句中有一条发生运行时错误，将停止执行批处理中当前语句和它之后的语句。这使得在实际应用批处理的过程中会产生错误的结果。实际上不同的运行时错误会导致不同的影响。

诸如算术溢出或约束冲突之类的运行时错误具有下面的影响：

1.大多数运行时错误将停止执行批处理中当前语句和它之后的语句。

2. 某些运行时错误（如违反约束）仅停止执行当前语句。而继续执行批处理中其他所有语句。

在遇到运行时错误的语句之前执行的语句不受影响。唯一例外的情况是批处理位于事务中并且错误导致事务回滚。在这种情况下，所有在运行时错误之前执行的未提交数据修改都将回滚。

下面针对算术溢出或约束冲突之类的运行时错误做了一些测试：

一．创建测试表，建立各种约束

USE tempdb

GO

IF OBJECT_ID('dbo.TestConstraint') IS NOT NULL

    DROP TABLE dbo.TestConstraint

GO

-- 创建测试表，建立主键、 NOT NULL 定义、 UNIQUE 约束、 CHECK 约束、 DEFAULT 约束

CREATE TABLE dbo.TestConstraint

(

 id INT NOT NULL PRIMARY KEY,

 username VARCHAR(20) NOT NULL CONSTRAINT UN_username UNIQUE(username),

 zipcode CHAR(6) NULL CONSTRAINT CH_zipcode CHECK (zipcode LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' ),

 Status [bit] NOT NULL CONSTRAINT [DF_TestConstraint_Status] DEFAULT ((0)),

 result TINYINT

)

GO

二、分别执行下面的批处理语句，观察批处理语句的执行情况

-- 违反 PRIMARY KEY 约束

INSERT INTO dbo.TestConstraint VALUES(1,'JACK','123456',0,3)

INSERT INTO dbo.TestConstraint VALUES(1,'JASON','123490',0,4)

SELECT @@ERROR

SELECT * FROM dbo.TestConstraint

GO

-- 违反 NOT NULL 定义

INSERT INTO dbo.TestConstraint VALUES(1,NULL,'123456',0,2)

SELECT @@ERROR

SELECT * FROM dbo.TestConstraint

GO

-- 违反了 UNIQUE KEY 约束

INSERT INTO dbo.TestConstraint VALUES(1,'JACK','123456',0,3)

INSERT INTO dbo.TestConstraint VALUES(2,'JACK','123490',0,4)

SELECT @@ERROR

SELECT * FROM dbo.TestConstraint

GO

-- 违反 CHECK 约束

INSERT INTO dbo.TestConstraint VALUES(1,'A','abcdef',0,1)

SELECT @@ERROR

SELECT * FROM dbo.TestConstraint

GO

-- 算术溢出错误

INSERT INTO dbo.TestConstraint VALUES(99,'JACK','123456',0,300)

SELECT @@ERROR

INSERT INTO dbo.TestConstraint VALUES(99,'JACK1','123456',0,3)

SELECT * FROM dbo.TestConstraint

GO

三．解决办法，达到出现错误时退出批处理

-- 解决办法 , 将批处理加入 TRY 块

BEGIN TRY

INSERT INTO dbo.TestConstraint VALUES(13, 'JACK','123456',0,300)

INSERT INTO dbo.TestConstraint VALUES(13, 'JACK','123456',0,300)

SELECT @@ERROR

INSERT INTO dbo.TestConstraint VALUES(22, 'JACK','123456',0,1.2)

END TRY

BEGIN CATCH

SELECT ERROR_MESSAGE(),ERROR_SEVERITY()

SELECT * FROM dbo.TestConstraint

END CATCH

四．完成后，运行下面的代码进行清理

-- 清理测试表

IF OBJECT_ID('dbo.TestConstraint') IS NOT NULL

    DROP TABLE dbo.TestConstraint

GO

 

总结：以上批处理都在运行时产生了错误，都是仅停止执行当前语句。而继续执行批处理中其他所有语句。为了使批处理遇到运行时错误就停止执行批处理中当前语句和它之后的语句。可以将批处理加入到TRY块中，如果 TRY 块内部发生错误，则会将控制传递给 CATCH 块中包含的另一个语句组。

参考SQL Server 的联机丛书http://technet.microsoft.com/zh-cn/library/ms175502.aspx