用 @@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与 @@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 检测几条语句的成功 下面的示例取决于 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与 @@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
但我觉的这样太浪费了,是不是有更好的方法?
下面的示例取决于 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与 @@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
下面的示例取决于 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与 @@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