SQL Server stored procedure try Catch TRANSACTION (GO)

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

2. ALTER PROC usp_accounttransaction
6. @AccountNum INT,
10. @Amount DECIMAL
14. As
18. BEGIN
22. BEGIN try --start the try Block.
26. BEGIN TRANSACTION -Start the TRANSACTION.
30. UPDATE mychecking SET Amount = amount-@Amount
34. WHERE AccountNum = @AccountNum
38. UPDATE mysavings SET Amount = Amount + @Amount
42. WHERE AccountNum = @AccountNum
46. COMMIT TRAN --Transaction success!
50. END TRY
54. BEGIN CATCH
58. IF @ @TRANCOUNT > 0
62. ROLLBACK TRAN --rollback in case of Error
66. --Can Raise ERROR with RaiseError () Statement including the details of the exception
70. --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)