SQLserver存储过程异常处理.txt

CREATE TABLE ErrorLog
(
         ID             INT IDENTITY (1,1) NOT NULL,
         ErrorNumber    INT,
         ErrorMessage   VARCHAR(1000),
         ErrorSeverity  INT,
         ErrorState     INT,
         ErrorLine      INT,
         ErrorProcedure VARCHAR(128),
         ErrorDate      datetime
);

CREATE PROCEDURE CatchErrorDemo
AS --相关业务逻辑

    BEGIN TRY
        SELECT  1 / 0;
    END TRY
--相关错误捕获
    BEGIN CATCH
        PRINT 'Error Number:' + CAST(ERROR_NUMBER() AS VARCHAR(10));
        PRINT 'Error Serverity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10));
        PRINT 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10));
        PRINT 'Error Procedure: ' + ERROR_PROCEDURE();
        PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10));
        PRINT 'Error Message: ' + ERROR_MESSAGE();
        INSERT  INTO ErrorLog
                ( ErrorNumber ,
                  ErrorSeverity ,
                  ErrorState ,

                  ErrorProcedure ,
                  ErrorLine ,
                  ErrorMessage ,
                  ErrorDate
                )
        VALUES  ( ERROR_NUMBER() ,
                  ERROR_SEVERITY() ,
                  ERROR_STATE() ,
                  ERROR_PROCEDURE() ,
                  ERROR_LINE() ,
                  ERROR_MESSAGE() ,
                  GETDATE()
                )

    END CATCH;



ALTER PROC usp_AccountTransaction  
  
    @AccountNum INT,  
  
    @Amount DECIMAL  
  
AS  
  
BEGIN  
  
    BEGIN TRY --Start the Try Block..