sqlserver 存儲(chǔ)過(guò)程 try catch TRANSACTION (轉(zhuǎn))
CREATE PROCEDURE YourProcedure????
AS
BEGIN
??? SET NOCOUNT ON;
??? BEGIN TRY---------------------開(kāi)始捕捉異常
?????? BEIN TRAN------------------開(kāi)始事務(wù)
??????? 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 -------提交事務(wù)
??? END TRY-----------結(jié)束捕捉異常
??? BEGIN CATCH------------有異常被捕獲
??????? IF @@TRANCOUNT > 0---------------判斷有沒(méi)有事務(wù)
??????? BEGIN
??????????? ROLLBACK TRAN----------回滾事務(wù)
??????? END?
??????? EXEC YourLogErrorProcedure-----------執(zhí)行存儲(chǔ)過(guò)程將錯(cuò)誤信息記錄在表當(dāng)中
??? END CATCH--------結(jié)束異常處理
END
?
---------------------------------------------記錄操作錯(cuò)信息的存儲(chǔ)過(guò)程--------------------------------------------
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;-----------------打印錯(cuò)誤信息的存儲(chǔ)過(guò)程
??????? 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]
?
本文來(lái)自CSDN博客，轉(zhuǎn)載請(qǐng)標(biāo)明出處：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.
??????? );
?
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.獲得錯(cuò)誤信息的函數(shù)表:??
下面系統(tǒng)函數(shù)在CATCH塊有效.可以用來(lái)得到更多的錯(cuò)誤信息:
下載文檔
