SQL Server stored procedure try Catch TRANSACTION (GO)

Original: http://www.cnblogs.com/yun_shuai/archive/2010/09/20/1831546.html

/*

1. Minor error (Severity level 0-10): By default, the client program is not sent an error message to continue working. Which means it can't be caught in a catch.

2. Medium error (Severity level 11-19): Can be caught (either in T-SQL or in the client program)

3. Critical error (Severity level 20-25): SQL Server will force the connection to be turned off. Obviously, it's not going to catch up.


"Key Tips!!" 】

Because of complex business or system performance problems, database SQL statements are executed longer.

Causes the client page to have a connection timeout (as set to 30 seconds)

At this time the database batch statement is not completed, the customer session disconnected, equivalent to interrupt operation, similar to "Case 1" of the interruption.

The transaction is not committed or rolled back, the resource is still in use, causing a blockage or deadlock! ~



"Workaround":

Precede the batch statement with SET xact_abort on
When the client is interrupted, the rollback operation is not completed and the resource is released in time.

--See if xact_abort is open
SELECT (16384 & @ @OPTIONS) = 16384 Then ' on ' ELSE ' OFF ' END) as Xact_abort;

*/

CREATE PROCEDURE Yourprocedure
As
BEGIN
SET NOCOUNT on;

Begin TRY---------------------Start catching exceptions
BEIN TRAN------------------Start a transaction
UPDATE A SET a.names = b.names from table 1 as A INNER JOIN table 2 as B on a.id = b.ID

UPDATE A SET a.names = b.names from table 1 as A INNER JOIN table 2 as B on a.test = B.test

Commit TRAN-------COMMIT Transaction
End TRY-----------Ending catch exception
BEGIN catch------------An exception is caught
If @ @TRANCOUNT > 0---------------Determine if there is a transaction
BEGIN
ROLLBACK TRAN----------ROLLBACK TRANSACTION
END
EXEC yourlogerrorprocedure-----------Execute stored procedure to record error information in a table
End CATCH--------Ending exception handling
END

---------------------------------------------Records stored procedures that manipulate the wrong information--------------------------------------------

CREATE PROCEDURE Yourlogerrorprocedure
@ErrorLogID [int] = 0 OUTPUT-Contains the errorlogid of the row inserted
As--by uspLogError in the errorlog table
BEGIN
SET NOCOUNT on;

--Output parameter value of 0 indicates that error
--Information was not logged
SET @ErrorLogID = 0;

BEGIN TRY
--Return If there is no error information to log
IF Error_number () is NULL
RETURN;

--Return If inside an uncommittable transaction.
--Data insertion/modification is no allowed when
--a transaction is in the uncommittable state.
IF xact_state () =-1
BEGIN
PRINT ' cannot log error since the current transaction was in a uncommittable state. ‘
+ ' Rollback the transaction before executing uspLogError in order to successfully log error information. ';
RETURN;
END

INSERT [dbo]. [Operateerrorlog]
(
[Operatename],
[ErrorNumber],
[ErrorSeverity],
[ErrorState],
[Errorprocedure],
[ErrorLine],
[ErrorMessage]
)
VALUES
(
CONVERT (sysname, current_user),
Error_number (),
Error_severity (),
Error_state (),
Error_procedure (),
Error_line (),
Error_message ()
);
SET @ErrorLogID = @ @IDENTITY;
END TRY
BEGIN CATCH
PRINT ' An error occurred in stored procedure usplogerror: ';
EXECUTE yourprinterrorprocedure;-----------------stored procedure for printing error messages
RETURN-1;
END CATCH
END;

CREATE PROCEDURE Yourprinterrorprocedure
As
BEGIN
SET NOCOUNT on;

   -Print error information. 
    print ' ERROR ' + CONVERT (varchar (50), Error_number ()) +
          ', Severity ' + CONVERT (varchar (5), ERROR _severity ()) +
          ', state ' + CONVERT (varchar (5), error_state ()) + 
          ', Procedure ' + ISNULL (error_procedure (), '-') +&NBSP
          ', line ' + CONVERT (varchar (5), Error_line ());
    PRINT error_message ();
END;

CREATE TABLE [dbo]. [Errorlog] (
[Errorlogid] [INT] IDENTITY (*) is not NULL,
[Errortime] [DateTime] Not NULL CONSTRAINT [Df_errorlog_errortime] DEFAULT (getdate ()),
[UserName] [sysname] COLLATE chinese_prc_ci_as not NULL,
[ErrorNumber] [INT] Not NULL,
[ErrorSeverity] [INT] Null
[ErrorState] [INT] Null
[Errorprocedure] [nvarchar] (126) COLLATE chinese_prc_ci_as NULL,
[ErrorLine] [INT] Null
[ErrorMessage] [nvarchar] (4000) COLLATE chinese_prc_ci_as not NULL,
CONSTRAINT [Pk_errorlog_errorlogid] PRIMARY KEY CLUSTERED
(
[Errorlogid] Asc
) with (Ignore_dup_key = OFF) on [PRIMARY]
) on [PRIMARY]

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/WeiZhang_son_Ding/archive/2010/02/05/5291732.aspx

Http://www.cnblogs.com/BpLoveGcy/archive/2010/03/22/1691407.html

ALTER PROC usp_accounttransaction

2. @AccountNum INT,
4. @Amount DECIMAL
6. As
8. BEGIN
10. BEGIN try --start the try Block.
12. BEGIN TRANSACTION -Start the TRANSACTION.
14. UPDATE mychecking SET Amount = amount-@Amount
16. WHERE AccountNum = @AccountNum
18. UPDATE mysavings SET Amount = Amount + @Amount
20. WHERE AccountNum = @AccountNum
22. COMMIT TRAN --Transaction success!
24. END TRY
26. BEGIN CATCH
28. IF @ @TRANCOUNT > 0
30. ROLLBACK TRAN --rollback in case of Error
32. --You can Raise ERROR with RaiseError () Statement including the details of the exception
34. --raiserror (Error_message (), Error_severity (), 1)

DECLARE @ErrorMessage NVARCHAR (4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT
@ErrorMessage = Error_message (),
@ErrorSeverity = Error_severity (),
@ErrorState = Error_state ();

--use RAISERROR inside the CATCH block to return error
--Information about the original error that caused
--execution to the CATCH block.
RAISERROR (@ErrorMessage,--Message text.
@ErrorSeverity,--Severity.
@ErrorState-State.
);

2. END CATCH
4. END
6. GO

2. BEGIN TRY
6. SELECT GETDATE ()
10. SELECT 1/0--evergreen divide by zero example!
14. END TRY
18. BEGIN CATCH
22. SELECT ' There is an error! ' + error_message ()
26. RETURN
30. END CATCH;

2. The function table that obtains the error message:

The following system functions are valid in the CATCH block. Can be used to get more error messages:

function	Description
Error_number ()	Returns the error number of the error message that caused the CATCH block to run.
Error_severity ()	Returns the severity level of the error message that caused the CATCH block to run
Error_state ()	Returns the status number of the error message that caused the CATCH block to run
Error_procedure ()	Returns the name of the stored procedure in which the error occurred
Error_line ()	Returns the line number where the error occurred
Error_message ()	Returns the full text of the error message that caused the CATCH block to run

• BEGIN TRY
• Try Statement 1
• Try Statement 2
• ...
• Try Statement M
• END TRY
• BEGIN CATCH
• Catch Statement 1
• Catch Statement 2
• ...
• Catch Statement N
• END CATCH

SQL Server stored procedure try Catch TRANSACTION (GO)