CREATE PROCEDURE YourProcedure????
AS
BEGIN
??? SET NOCOUNT ON;

??? BEGIN TRY---------------------開始捕捉異常
?????? BEIN TRAN------------------開始事務
??????? UPDATE A SET A.names = B.names FROM 表1 AS A INNER JOIN 表2 AS B ON A.id = B.id

??????? UPDATE A SET A.names = B.names FROM 表1 AS A INNER JOIN 表2 AS B ON A.TEST = B.TEST

??? COMMIT TRAN -------提交事務
??? END TRY-----------結(jié)束捕捉異常
??? BEGIN CATCH------------有異常被捕獲
??????? IF @@TRANCOUNT > 0---------------判斷有沒有事務
??????? BEGIN
??????????? ROLLBACK TRAN----------回滾事務
??????? END?
??????? EXEC YourLogErrorProcedure-----------執(zhí)行存儲過程將錯誤信息記錄在表當中
??? END CATCH--------結(jié)束異常處理
END

?

---------------------------------------------記錄操作錯信息的存儲過程--------------------------------------------

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 not allowed when?
??????? -- a transaction is in an uncommittable state.
??????? IF XACT_STATE() = -1
??????? BEGIN
??????????? PRINT 'Cannot log error since the current transaction is in an 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;-----------------打印錯誤信息的存儲過程
??????? 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(), '-') +?
????????? ', Line ' + CONVERT(varchar(5), ERROR_LINE());
??? PRINT ERROR_MESSAGE();
END;

CREATE TABLE [dbo].[ErrorLog](
??? [ErrorLogID] [int] IDENTITY(1,1) 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]

?

本文來自CSDN博客，轉(zhuǎn)載請標明出處：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?? ?? ????@AccountNum?INT,?? ?? ????@Amount?DECIMAL?? ?? AS?? ?? BEGIN?? ?? ????BEGIN?TRY?--Start?the?Try?Block..?? ?? ????????BEGIN?TRANSACTION?--?Start?the?transaction..?? ?? ????????????UPDATE?MyChecking?SET?Amount?=?Amount?-?@Amount?? ?? ????????????????WHERE?AccountNum?=?@AccountNum?? ?? ????????????UPDATE?MySavings?SET?Amount?=?Amount?+?@Amount?? ?? ????????????????WHERE?AccountNum?=?@AccountNum?? ?? ????????COMMIT?TRAN?--?Transaction?Success!?? ?? ????END?TRY?? ?? ????BEGIN?CATCH?? ?? ????????IF?@@TRANCOUNT?>?0?? ?? ????????????ROLLBACK?TRAN?--RollBack?in?case?of?Error?? ?? ????????--?you?can?Raise?ERROR?with?RAISEERROR()?Statement?including?the?details?of?the?exception?? ?? ????????--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 jump to the CATCH block.
???　　RAISERROR (@ErrorMessage, -- Message text.
??????? 　　@ErrorSeverity, -- Severity.
??????? 　　@ErrorState -- State.
???????　　 );

?? ????END?CATCH?? ?? END?? ?? GO??

?

BEGIN?TRY?? ?? ????SELECT?GETDATE()?? ?? ????SELECT?1/0--Evergreen?divide?by?zero?example!?? ?? END?TRY?? ?? BEGIN?CATCH?? ?? ????SELECT?'There?was?an?error!?'?+?ERROR_MESSAGE()?? ?? ????RETURN?? ?? END?CATCH;??

?

2.獲得錯誤信息的函數(shù)表:?

?

下面系統(tǒng)函數(shù)在CATCH塊有效.可以用來得到更多的錯誤信息:

函數(shù) 描述 ERROR_NUMBER() 返回導致運行 CATCH 塊的錯誤消息的錯誤號。 ERROR_SEVERITY() 返回導致 CATCH 塊運行的錯誤消息的嚴重級別 ERROR_STATE() 返回導致 CATCH 塊運行的錯誤消息的狀態(tài)號 ERROR_PROCEDURE() 返回出現(xiàn)錯誤的存儲過程名稱 ERROR_LINE() 返回發(fā)生錯誤的行號 ERROR_MESSAGE() 返回導致 CATCH 塊運行的錯誤消息的完整文本

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