raiserror
的作用: raiserror
是用于抛出一个错误。[ 以下资料来源于sql server 2005的帮助 ]
其语法如下:
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ]
)
[ WITH option [ ,...n ] ]
简要说明一下:
第一个参数:{ msg_id | msg_str | @local_variable }
msg_id
:表示可以是一个sys.messages
表中定义的消息代号;
使用 sp_addmessage
存储在 sys.messages
目录视图中的用户定义错误消息号。
用户定义错误消息的错误号应当大于 50000
。
msg_str
:表示也可以是一个用户定义消息,该错误消息最长可以有 2047 个字符;
(如果是常量,请使用N’xxxx’,因为是nvarchar
的)
当指定 msg_str
时,RAISERROR
将引发一个错误号为 5000
的错误消息。
@local_variable
:表示也可以是按照 msg_str
方式的格式化字符串变量。
第二个参数:severity
用户定义的与该消息关联的严重级别。(这个很重要)
任何用户都可以指定 0 到 18 之间的严重级别。
[0,10]
的闭区间内,不会跳到catch
;
如果是[11,19],则跳到catch
;
如果[20,无穷),则直接终止数据库连接;
第三个参数:state
如果在多个位置引发相同的用户定义错误,
则针对每个位置使用唯一的状态号有助于找到引发错误的代码段。
介于 1 至 127 之间的任意整数。(state 默认值为1)
当state 值为 0 或大于 127 时会生成错误!
第四个参数:argument
用于代替 msg_str
或对应于 msg_id
的消息中的定义的变量的参数。
第五个参数:option
错误的自定义选项,可以是下表中的任一值:
LOG
:在错误日志和应用程序日志中记录错误;
NOWAIT
:将消息立即发送给客户端;
SETERROR
:将 @@ERROR
值和 ERROR_NUMBER
值设置为 msg_id
或 50000
;
代码示例
--示例1
DECLARE @raiseErrorCode nvarchar(50)
SET @raiseErrorCode = CONVERT(nvarchar(50), YOUR UNIQUEIDENTIFIER KEY)
RAISERROR('%s INVALID ID. There is no record in table',16,1, @raiseErrorCode)
--示例2
RAISERROR (
N'This is message %s %d.', -- Message text,
10, -- Severity,
1, -- State,
N'number', -- First argument.
5 -- Second argument.
);
-- The message text returned is: This is message number 5.
GO
--示例3
RAISERROR (N'<<%*.*s>>', -- Message text.
10, -- Severity,
1, -- State,
7, -- First argument used for width.
3, -- Second argument used for precision.
N'abcde'); -- Third argument supplies the string.
-- The message text returned is: << abc>>.
GO
--示例4
RAISERROR (N'<<%7.3s>>', -- Message text.
10, -- Severity,
1, -- State,
N'abcde'); -- First argument supplies the string.
-- The message text returned is: << abc>>.
GO
--示例5
--A. 从 CATCH 块返回错误消息
--以下代码示例显示如何在 TRY 块中使用 RAISERROR 使执行跳至关联的 CATCH 块中。
--它还显示如何使用 RAISERROR 返回有关调用 CATCH 块的错误的信息。
BEGIN TRY
RAISERROR ('Error raised in TRY block.', -- Message text.
16, -- Severity.
1 -- State.
);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
--示例6
--B. 在 sys.messages 中创建即席消息
--以下示例显示如何引发 sys.messages 目录视图中存储的消息。
--该消息通过 sp_addmessage 系统存储过程,以消息号50005添加到 sys.messages 目录视图中。
sp_addmessage @msgnum = 50005,
@severity = 10,
@msgtext = N'<<%7.3s>>';
GO
RAISERROR (50005, -- Message id.
10, -- Severity,
1, -- State,
N'abcde'); -- First argument supplies the string.
-- The message text returned is: << abc>>.
GO
sp_dropmessage @msgnum = 50005;
GO
--示例7
--C. 使用局部变量提供消息文本
--以下代码示例显示如何使用局部变量为 RAISERROR 语句提供消息文本。
sp_addmessage @msgnum = 50005,
@severity = 10,
@msgtext = N'<<%7.3s>>';
GO
RAISERROR (50005, -- Message id.
10, -- Severity,
1, -- State,
N'abcde'); -- First argument supplies the string.
-- The message text returned is: << abc>>.
GO
sp_dropmessage @msgnum = 50005;
GO
同时在存储过程中将事务和try…catch
联合使用
在存储过程中使用事务时,如果没有try…catch
语句,那么当set xact_abort on
时,如果有错误发生,在批处理语句结束后,系统会自动回滚所有的sql操作。当set xact_abort off
时,如果有错误发生,在批处理语句结束后,系统会执行所有没有发生错误的语句,发生错误的语句将不会被执行。
在存储过程中使用事务时,如果存在try…catch
语句块,那么当捕获到错误时,需要在catch
语句块中手动进行Rollback
操作,否则系统会给客户端传递一条错误信息。如果在存储过程开始处将set xact_abort on
,那么当有错误发生时,系统会将当前事务置为不可提交状态,即会将xact_state()
置为-1
,此时只可以对事务进行Rollback
操作,不可进行提交(commit
)操作,那么我们在catch
语句块中就可以根据xact_state()
的值来判断是否有事务处于不可提交状态,如果有则可以进行rollback
操作了。如果在存储过程开始处将set xact_abort off
,那么当有错误发生时,系统不会讲xact_state()
置为-1
,那么我们在catch
块中就不可以根据该函数值来判断是否需要进行rollback
了,但是我们可以根据@@Trancount
全局变量来判断,如果在catch
块中判断出@@Trancount
数值大于0
,代表还有未提交的事务,既然进入catch
语句块了,那么还存在未提交的事务,该事务应该是需要rollback
的,但是这种方法在某些情况下可能判断的不准确。推荐的方法还是将set xact_abort on
,然后在catch
中判断xact_state()
的值来判断是否需要Rollback
操作。
下面我们来看看两个例子:
一.使用Set xact_abort on
Create proc myProcedure
As
begin
set xact_abort on;
begin try
begin tran
insert into TestStu values('Terry','boy',23);
insert into TestStu values('Mary','girl',21);
commit tran
end try
begin catch
-- 在此可以使用xact_state()来判断是否有不可提交的事务,不可提交的事务
-- 表示在事务内部发生错误了。Xact_state()有三种值: - 1 .事务不可提交;
-- 1 .事务可提交; 0 .表示没有事务,此时commit或者rollback会报错。
if xact_state() =- 1
rollback tran;
end catch
end
二.使用Set xact_abort off
CREATE PROC Myprocedure
AS
BEGIN
SET xact_abort OFF;
BEGIN try
BEGIN TRAN
INSERT INTO teststu
VALUES ( ' Terry ',
' boy ',
23 );
INSERT INTO teststu
VALUES ( ' Mary ',
' girl ',
21 );
COMMIT TRAN
END try
BEGIN catch
-- 在此不可以使用xact_state来判断是否有不可提交的事务
-- 只可以使用@@Trancount来判断是否有还未提交的事务,未提交的事务未必
-- 就是不可提交的事务,所以使用@@TranCount > 0后就RollBack是不准确的
IF @@TranCount > 0
ROLLBACK TRAN;
END catch
END
另外,对于@@Trancount
需要说明的是,begin tran
语句将 @@Trancount
加 1
。Rollback tran
将 @@Trancount
递减
存储过程中使用事务的简单语法
在存储过程中使用事务时非常重要的,使用数据可以保持数据的关联完整性,在Sql server
存储过程中使用事务也很简单,用一个例子来说明它的语法格式:
Create Procedure MyProcedure
( @Param1 nvarchar(10),
@param2 nvarchar(10) )
AS
Begin
Set NOCOUNT ON;
Set XACT_ABORT ON;
Begin Tran
Delete from table1 where name=’abc’;
Insert into table2 values(value1,value2,value3);
Commit Tran
End
说明:
1 、使用存储过程执行事物,需要开启XACT_ABORT
参数(默认值为Off),将该参数设置为On,表示当执行事务时,如果出错,会将transcation
设置为uncommittable
状态,那么在语句块批处理结束后将回滚所有操作;如果该参数设置为Off,表示当执行事务时,如果出错,出错的语句将不会执行,其他正确的操作继续执行。
2、当SET NOCOUNT
为 ON
时,不返回计数(计数表示受 Transact-SQL
语句影响的行数,例如在Sql server
查询分析器中执行一个delete
操作后,下方窗口会提示(3)Rows Affected)。当 SET NOCOUNT
为 OFF
时,返回计数,我们应该在存储过程的头部加上SET NOCOUNT ON
这样的话,在退出存储过程的时候加上 SET NOCOUNT OFF
这样的话,以达到优化存储过程的目的。
二、事务内设置保存点
用户可以在事务内设置保存点或标记。保存点定义如果有条件地取消事务的一部分,事务可以返回的位置。如果将事务回滚到保存点,则必须(如果需要,使用更多的 Transact-SQL 语句和 COMMIT TRANSACTION
语句)继续完成事务,或者必须(通过将事务回滚到其起始点)完全取消事务。若要取消整个事务,请使用 ROLLBACK TRANSACTION transaction_name
格式。这将撤消事务的所有语句和过程。如:
代码
CREATE PROCEDURE Myprocedure
AS
BEGIN
SET nocount ON;
SET xact_abort ON;
BEGIN TRAN ok
-- 开始一个事务OK
DELETE FROM rxqz
WHERE qz = ' rx015 '
-- 删除数据
SAVE TRAN bcd -- 保存一个事务点命名为bcd
UPDATE sz
SET NAME = ' 李丽s '
WHERE NAME = ' 李丽 ' -- 修改数据
IF @@error <> 0 -- 判断修改数据有没有出错
BEGIN -- 如果出错
ROLLBACK TRAN bcd -- 回滚事务到BCD 的还原点
COMMIT TRAN ok -- 提交事务
END
ELSE -- 没有出错
COMMIT TRAN ok -- 提交事务
END
说明:1、@@error
判断是否有错误,为0
表示没有错误,但是对那种重大错误无法捕捉,而且@@error
只能前一句sql
语句生效。
三、存储过程使用try…catch
捕获错误
在存储过程中可以使用try…catch
语句来捕获错误,如下:
CREATE PROCEDURE Myprocedure (@Param1 NVARCHAR( 10 ),
@param2 NVARCHAR( 10 ))
AS
BEGIN
SET nocount ON;
BEGIN try
DELETE FROM table1
WHERE NAME = ’abc’;
INSERT INTO table2
VALUES (value1,
value2,
value3);
END try
BEGIN catch
SELECT Error_number() AS ErrorNumber,
Error_message() AS ErrorMessage;
END catch
END
说明:
1、捕获错误的函数有很多,如下:
ERROR_NUMBER()
返回错误号。
ERROR_SEVERITY()
返回严重性。
ERROR_STATE()
返回错误状态号。
ERROR_PROCEDURE()
返回出现错误的存储过程或触发器的名称。
ERROR_LINE()
返回导致错误的例程中的行号。
ERROR_MESSAGE()
返回错误消息的完整文本。该文本可包括任何可替换参数所提供的值,如长度、对象名或时间。
2、有些错误,如sql
语句中的表名称输入错误,这是数据库引擎无法解析这个表名称时,所发生的错误在当前的try…catch
语句中无法捕获,必须由外层调用该存储过程的地方使用 try…catch
来进行捕获。