Home  >  Article  >  Database  >  sqlserver 存储过程 try catch TRANSACTION (转)

sqlserver 存储过程 try catch TRANSACTION (转)

WBOY
WBOYOriginal
2016-06-07 15:46:151526browse

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.   
  3.     @AccountNum INT,  
  4.   
  5.     @Amount DECIMAL  
  6.   
  7. AS  
  8.   
  9. BEGIN  
  10.   
  11.     BEGIN TRY --Start the Try Block..  
  12.   
  13.         BEGIN TRANSACTION -- Start the transaction..  
  14.   
  15.             UPDATE MyChecking SET Amount = Amount - @Amount  
  16.   
  17.                 WHERE AccountNum = @AccountNum  
  18.   
  19.             UPDATE MySavings SET Amount = Amount + @Amount  
  20.   
  21.                 WHERE AccountNum = @AccountNum  
  22.   
  23.         COMMIT TRAN -- Transaction Success!  
  24.   
  25.     END TRY  
  26.   
  27.     BEGIN CATCH  
  28.   
  29.         IF @@TRANCOUNT > 0  
  30.   
  31.             ROLLBACK TRAN --RollBack in case of Error  
  32.   
  33.         -- you can Raise ERROR with RAISEERROR() Statement including the details of the exception  
  34.   
  35.         --RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)  
  36.      

            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.   
  2.     END CATCH  
  3.   
  4. END  
  5.   
  6. GO  

 

  1. BEGIN TRY  
  2.   
  3.     SELECT GETDATE()  
  4.   
  5.     SELECT 1/0--Evergreen divide by zero example!  
  6.   
  7. END TRY  
  8.   
  9. BEGIN CATCH  
  10.   
  11.     SELECT 'There was an error! ' + ERROR_MESSAGE()  
  12.   
  13.     RETURN  
  14.   
  15. 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  
  • Statement:
    The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn