当前位置: 澳门新濠3559 > 数据库 > 正文

--exec 测试用勿删  1,    END TRY-----------结束捕

时间:2019-10-12 05:05来源:数据库
-- ============================================= --带参输出的存储过程 --测试方法: --declare @return int --exec 测试用勿删  1, @return out --print @return -- ============================================= 建一个表  t

-- =============================================
--带参输出的存储过程
--测试方法:
--declare @return int
--exec 测试用勿删  1, @return out
--print @return
-- =============================================

建一个表  t_hoteladvertise
有如下字段:
ID  自动增长(主要)
SortID  INT (主要)
--exec 测试用勿删  1,    END TRY-----------结束捕捉异常。Title  NVARCHAR(50)
代码:

CREATE PROCEDURE YourProcedure    
AS
BEGIN
    SET NOCOUNT ON;

Create PROCEDURE [dbo].[测试用勿删]
@userid  int,    
@return int    out
AS
begin try
    BEGIN TRAN
    print 1/1
    COMMIT TRAN
    print '成功了'
END try
BEGIN catch
   ROLLBACK
   print '回滚了'
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

CREATE proc sp_ehotel_DownAdvertise
@ID INT
as
DECLARE @SortID int
DECLARE @TempSortID INT
DECLARE @TempID INT
BEGIN  TRAN
SELECT @SortID=SortID  from t_hoteladvertise where [ID]=@ID
SELECT @TempSortID=max(SortID) from t_hoteladvertise where SortID>@SortID
SELECT @TempID=[ID] from t_hoteladvertise WHERE SortID=@TempSortID
if @@error>0 or @@rowcount<>1
goto NeedRollBack
UPDATE t_hoteladvertise SET SortID=@SortID WHERE [ID]=@TempID
if @@error>0 or @@rowcount<>1
goto NeedRollBack
UPDATE t_hoteladvertise SET SortID=@TempSortID where [ID]=@ID
NeedRollBack:
if @@error>0 or @@rowcount<>1
  rollback tran
else
commit tran
GO

    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

    SELECT
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();

CREATE proc sp_ehotel_UpAdvertise
@ID INT
as
DECLARE @SortID int
DECLARE @TempSortID INT
DECLARE @TempID INT
BEGIN  TRAN
SELECT @SortID=SortID  from t_hoteladvertise where [ID]=@ID
SELECT @TempSortID=max(SortID) from t_hoteladvertise where SortID<@SortID
SELECT @TempID=[ID] from t_hoteladvertise WHERE SortID=@TempSortID
if @@error>0 or @@rowcount<>1
goto NeedRollBack
UPDATE t_hoteladvertise SET SortID=@SortID WHERE [ID]=@TempID
if @@error>0 or @@rowcount<>1
goto NeedRollBack
UPDATE t_hoteladvertise SET SortID=@TempSortID where [ID]=@ID
NeedRollBack:
if @@error>0 or @@rowcount<>1
  rollback tran
else
commit tran
GO
代码段:
代码:

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

    RAISERROR (@ErrorMessage, -- Message text.
    @ErrorSeverity, -- Severity.
    @ErrorState -- State.
    );
end catch
set @return=@userid 20

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        SqlConnection conn = new SqlConnection();
       if (e.CommandName == "down")
        {
            int IndexID = Convert.ToInt32(e.CommandArgument);
            try
            {
                XMLData.OpenDatabase(conn);
                XMLData.ExecuteNonQuery(conn, "SP_EHOTEL_UPADVERTISE", IndexID);
                Label2.Visible = true;
                Label2.Text = "下移成功!";

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

            }
            catch (Exception)
            {
                Response.Write("<script language='javascript'>alert('下移出错,点取消返回');</" "script>");
            }
            finally
            {
                XMLData.CloseDatabase(conn);
                BindAdvertise();
            }
        }
        if (e.CommandName == "up")
        {
            int IndexID = Convert.ToInt32(e.CommandArgument);
            try
            {
                XMLData.OpenDatabase(conn);
                XMLData.ExecuteNonQuery(conn, "SP_EHOTEL_DOWNADVERTISE", IndexID);
                Label2.Visible = true;
                Label2.Text = "上移成功!";
            }

 

            catch (Exception)
            {
                Response.Write("<script language='javascript'>alert('上移出错,点取消返回');</" "script>");
            }
            finally
            {
                XMLData.CloseDatabase(conn);
                BindAdvertise();
            }
        }
    }

---------------------------------------------记录操作错信息的存储过程--------------------------------------------

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博客,转载请标明出处:

 

 

  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. 澳门新濠3559,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  

编辑:数据库 本文来源:--exec 测试用勿删  1,    END TRY-----------结束捕

关键词: 澳门新濠3559