http://msdn.microsoft.com/en-us/library/aa933181(v=sql.80).aspx
@@ERROR
在MSDN文檔中,我們可以看到,@@ERROR返回最近的那條SQL語句執行的結果。如果成功執行,@@ERROR=0。如果出現錯誤,@@ERROR=錯誤代號。
要注意的是,每條SQL語句執行后,都會改變@@ERROR的值。所以要麼你就在執行SQL語句后就趕緊判斷@@ERROR的有效性,要麼你就把這個值存入一個局部變量,以備以後使用。
1.用@@ERROR來檢查指定錯誤。
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"
這個例子是用來檢查在UPDATE的時候,出現的constraint violation(錯誤代碼547)錯誤。
2.用@@ERROR來選擇是否退出這個存儲過程。
這個IF…ELSE語句用來判斷在INSERT返回一個值代表這個存儲過程成功或失敗
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
3.用@@ERROR 來檢查幾條SQL語句的成功與否
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
用兩個個局部變量存儲@@ERROR的值,然後再進行判斷。
4.用@@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
5.用@@ERROR 來檢查事務是否成功執行。如果未成功,則回滾事務。
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
SET @ResultMessage = 'Finished'
SET @success = 1
END
ELSE
BEGIN
ROLLBACK TRANSACTION
SET @result = ISNULL((SELECT 'Error ' + cast(error as varchar) + '(' + cast(Severity as varchar) + '): ' + [Description] FROM master..sysmessages WHERE error = @SQLError), 'SQL Server error')
SET @ResultMessage = @result
SET @success = 0
END