@@ERROR
返回最后执行的 Transact
-
SQL 语句的错误代码。 语法
@@ERROR
返回类型
integer
注释 当 Microsoft® SQL Server™ 完成 Transact
-
SQL 语句的执行时,如果语句执行成功,则
@@ERROR
设置为
0
。若出现一个错误,则返回一条错误信息。
@@ERROR
返回此错误信息代码,直到另一条 Transact
-
SQL 语句被执行。您可以在 sysmessages 系统表中查看与
@@ERROR
错误代码相关的文本信息。 由于
@@ERROR
在每一条语句执行后被清除并且重置,应在语句验证后立即检查它,或将其保存到一个局部变量中以备事后查看。 示例 A.用
@@ERROR
检测一个特定错误 下面的示例用
@@ERROR
在一个
UPDATE
语句中检测限制检查冲突(错误 #
547
)。
USE
pubs
GO
UPDATE
authors
SET
au_id
=
'
172 32 1176
'
WHERE
au_id
=
"
172
-
32
-
1176
"
IF
@@ERROR
=
547
print
"A
check
constraint
violation occurred" B.用
@@ERROR
有条件地退出一个过程 在此示例中,
IF
...
ELSE
语句在存储过程中的
INSERT
语句后检测
@@ERROR
。
@@ERROR
变量的值将决定传给调用程序的返回值,以指示此过程的成功与失败。
USE
pubs
GO
--
Create the procedure.
CREATE
PROCEDURE
add_author
@au_id
varchar
(
11
),
@au_lname
varchar
(
40
),
@au_fname
varchar
(
20
),
@phone
char
(
12
),
@address
varchar
(
40
)
=
NULL
,
@city
varchar
(
20
)
=
NULL
,
@state
char
(
2
)
=
NULL
,
@zip
char
(
5
)
=
NULL
,
@contract
bit
=
NULL
AS
--
Execute the INSERT statement.
INSERT
INTO
authors (au_id, au_lname, au_fname, phone, address, city, state, zip, contract)
values
(
@au_id
,
@au_lname
,
@au_fname
,
@phone
,
@address
,
@city
,
@state
,
@zip
,
@contract
)
--
Test the error value.
IF
@@ERROR
<>
0
BEGIN
--
Return 99 to the calling program to indicate failure.
PRINT
"An error occurred loading the new author information"
RETURN
(
99
)
END
ELSE
BEGIN
--
Return 0 to the calling program to indicate success.
PRINT
"The new author information has been loaded"
RETURN
(
0
)
END
GO
C.用
@@ERROR
检测几条语句的成功 下面的示例取决于
INSERT
和
DELETE
语句的成功操作。局部变量在两条语句后均被设置为
@@ERROR
的值,并且用于此操作的共享错误处理例程中。
USE
pubs
GO
DECLARE
@del_error
int
,
@ins_error
int
--
Start a transaction.
BEGIN
TRAN
--
Execute the DELETE statement.
DELETE
authors
WHERE
au_id
=
'
409-56-7088
'
--
Set a variable to the error value for
--
the DELETE statement.
SELECT
@del_error
=
@@ERROR
--
Execute the INSERT statement.
INSERT
authors
VALUES
(
'
409-56-7008
'
,
'
Bennet
'
,
'
Abraham
'
,
'
415 658-9932
'
,
'
6223 Bateman St.
'
,
'
Berkeley
'
,
'
CA
'
,
'
94705
'
,
1
)
--
Set a variable to the error value for
--
the INSERT statement.
SELECT
@ins_error
=
@@ERROR
--
Test the error values.
IF
@del_error
=
0
AND
@ins_error
=
0
BEGIN
--
Success. Commit the transaction.
PRINT
"The author information has been replaced"
COMMIT
TRAN
END
ELSE
BEGIN
--
An error occurred. Indicate which operation(s) failed
--
and roll back the transaction.
IF
@del_error
<>
0
PRINT
"An error occurred during execution
of
the
DELETE
statement."
IF
@ins_error
<>
0
PRINT
"An error occurred during execution
of
the
INSERT
statement."
ROLLBACK
TRAN
END
GO
D. 与
@@ROWCOUNT
一同使用
@@ERROR
下面的示例用
@@ERROR
和
@@ROWCOUNT
验证一条
UPDATE
语句的操作。为任何可能出现的错误而检验
@@ERROR
的值,而用
@@ROWCOUNT
保证更新已成功应用于表中的某行。
USE
pubs
GO
CREATE
PROCEDURE
change_publisher
@title_id
tid,
@new_pub_id
char
(
4
)
AS
--
Declare variables used in error checking.
DECLARE
@error_var
int
,
@rowcount_var
int
--
Execute the UPDATE statement.
UPDATE
titles
SET
pub_id
=
@new_pub_id
WHERE
title_id
=
@title_id
--
Save the @@ERROR and @@ROWCOUNT values in local
--
variables before they are cleared.
SELECT
@error_var
=
@@ERROR
,
@rowcount_var
=
@@ROWCOUNT
--
Check for errors. If an invalid @new_pub_id was specified
--
the UPDATE statement returns a foreign-key violation error #547.
IF
@error_var
<>
0
BEGIN
IF
@error_var
=
547
BEGIN
PRINT
"ERROR: Invalid ID specified
for
new publisher"
RETURN
(
1
)
END
ELSE
BEGIN
PRINT
"ERROR: Unhandled error occurred"
RETURN
(
2
)
END
END
--
Check the rowcount. @rowcount_var is set to 0
--
if an invalid @title_id was specified.
IF
@rowcount_var
=
0
BEGIN
PRINT
"Warning: The title_id specified
is
not
valid"
RETURN
(
1
)
END
ELSE
BEGIN
PRINT
"The book has been updated
with
the new publisher"
RETURN
(
0
)
END
GO
@@ERROR使用方法
最新推荐文章于 2023-02-06 16:27:26 发布