欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

sqlserver 存储过程 try catch TRANSACTION (转)

程序员文章站 2022-05-15 22:25:31
...

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

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-----------结束捕捉异常
BEGIN CATCH------------有异常被捕获
IF @@TRANCOUNT > 0---------------判断有没有事务
BEGIN
ROLLBACK TRAN----------回滚事务
END
EXEC YourLogErrorProcedure-----------执行存储过程将错误信息记录在表当中
END CATCH--------结束异常处理
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]

http://blog.csdn.net/WeiZhang_son_Ding/archive/2010/02/05/5291732.aspx

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

  1. ALTER PROC usp_AccountTransaction
  2. @AccountNum INT,
  3. @Amount DECIMAL
  4. AS
  5. BEGIN
  6. BEGIN TRY --Start the Try Block..
  7. BEGIN TRANSACTION -- Start the transaction..
  8. UPDATE MyChecking SET Amount = Amount - @Amount
  9. WHERE AccountNum = @AccountNum
  10. UPDATE MySavings SET Amount = Amount + @Amount
  11. WHERE AccountNum = @AccountNum
  12. COMMIT TRAN -- Transaction Success!
  13. END TRY
  14. BEGIN CATCH
  15. IF @@TRANCOUNT > 0
  16. ROLLBACK TRAN --RollBack in case of Error
  17. -- you can Raise ERROR with RAISEERROR() Statement including the details of the exception
  18. --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.
   );

  1. END CATCH
  2. END
  3. GO

  1. BEGIN TRY
  2. SELECT GETDATE()
  3. SELECT 1/0--Evergreen divide by zero example!
  4. END TRY
  5. BEGIN CATCH
  6. SELECT 'There was an error! ' + ERROR_MESSAGE()
  7. RETURN
  8. END CATCH;

2.获得错误信息的函数表:

下面系统函数在CATCH块有效.可以用来得到更多的错误信息:

函数 描述
ERROR_NUMBER() 返回导致运行 CATCH 块的错误消息的错误号。
ERROR_SEVERITY() 返回导致 CATCH 块运行的错误消息的严重级别
ERROR_STATE() 返回导致 CATCH 块运行的错误消息的状态号
ERROR_PROCEDURE() 返回出现错误的存储过程名称
ERROR_LINE() 返回发生错误的行号
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