今天有个老朋友忽然问我一个问题,关于存储过程的。这个存储很简单:
CREATE
PROCEDURE
Proc_InsertTemp
( @number varchar ( 3000 ),
@name varchar ( 2100 ),
@output varchar ( 100 ) output )
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @outputerr varchar ( 3000 )
insert into dbo.Table_1 values ( @number , @name )
if @@error <> 0 or @@rowcount = 0
begin
select @output = ' -1 '
rollback transaction operTran1
return
end
else
begin
COMMIT TRANSACTION operTran1
end
END
( @number varchar ( 3000 ),
@name varchar ( 2100 ),
@output varchar ( 100 ) output )
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @outputerr varchar ( 3000 )
insert into dbo.Table_1 values ( @number , @name )
if @@error <> 0 or @@rowcount = 0
begin
select @output = ' -1 '
rollback transaction operTran1
return
end
else
begin
COMMIT TRANSACTION operTran1
end
END
表Table_1的结构也很简单:
CREATE
TABLE
[
dbo
]
.
[
Table_1
]
(
[ number ] [ int ] NULL ,
[ value ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL
)
[ number ] [ int ] NULL ,
[ value ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL
)
很简单吧?但是问题来了!他为了测试这个存储过程,这么调用的:
DECLARE
@return_value
int
,
@output varchar ( 100 )
EXEC @return_value = [ dbo ] . [ Proc_Psy_InsertUserInfoData ]
@number = N ' bbb ' , -- 注意这里使用的是错误的数据
@name = N ' aaaaaaa ' ,
@output = @output OUTPUT
SELECT @output as N ' @output '
@output varchar ( 100 )
EXEC @return_value = [ dbo ] . [ Proc_Psy_InsertUserInfoData ]
@number = N ' bbb ' , -- 注意这里使用的是错误的数据
@name = N ' aaaaaaa ' ,
@output = @output OUTPUT
SELECT @output as N ' @output '
按照他的想法,应该是事务回滚,输出错误代码-1。结果,确是得到这样的错误提示:
在将 varchar 值 'bbb' 转换成数据类型 int 时失败。
这个错误倒是有点儿意思,所以写下来记下。这个错误自然是出在insert这一句,只不过,出错之后,存储过程自动结束,当前事务完成会滚,后面的语句根本就不会执行,自然,事务也无法捕获这个错误了。
当Transact-SQL 语句出现运行时错误时,SQL Server 自动终止T-SqL的执行并回滚到当前事务。这是SQL Serverd的默认设置,这个设置由参数XACT-ABORT决定的。默认情况下,XACT_ABORT的值为ON,T-SQL自动终止并回滚当前事务。XACT_ABORT值为OFF时,如果遇到外键约束等参照完整性错误时,只会回滚产生错误的T-SQL语句,而当前事务继续进行处理。而如果遇到很严重的错误,SQL Server仍会回滚整个事务。
Sql Server 2005中,T-SQL还借鉴了高级语言中的TRY-CATCH结构,引入了这种强大的错误捕获机制。就已上面的这个存储过程为例,在insert语句前后插入TRY-CATCH语句,就可以捕获错误了:
CREATE
PROCEDURE
Proc_InsertTemp
( @number varchar ( 3000 ),
@name varchar ( 2100 ),
@output varchar ( 100 ) output )
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @outputerr varchar ( 3000 )
BEGIN TRY
insert into dbo.Table_1 values ( @number , @name )
END TRY
BEGIN CATCH
-- 在这里添加错误处理语句
END CATCH
if @@error <> 0 or @@rowcount = 0
begin
select @output = ' -1 '
rollback transaction operTran1
return
end
else
begin
COMMIT TRANSACTION operTran1
end
END
( @number varchar ( 3000 ),
@name varchar ( 2100 ),
@output varchar ( 100 ) output )
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @outputerr varchar ( 3000 )
BEGIN TRY
insert into dbo.Table_1 values ( @number , @name )
END TRY
BEGIN CATCH
-- 在这里添加错误处理语句
END CATCH
if @@error <> 0 or @@rowcount = 0
begin
select @output = ' -1 '
rollback transaction operTran1
return
end
else
begin
COMMIT TRANSACTION operTran1
end
END