[MS SQL]存储过程实例讲解-侦错

http://msdn.microsoft.com/en-us/library/aa933181(v=sql.80).aspx

 在SQL的存儲過程中，如果檢測某條SQL語句是否正確執行呢？這時候我們要用到非常有用的SQL Server的保留字

@@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