存储过程中的错误处理 [英] Error Handling in Store Procedure

问题描述

我正在使用ASP.NET 3.5和SQL2005.我想在存储过程中使用错误处理，我可以在点网代码中使用它.我的代码没有给我正确的输出.有人可以看到我在做什么错吗?

I am working on ASP.NET 3.5 and SQL 2005. I want to use error handling in store procedure that i can use in my dot net code. My code is not giving me correct output. Could any one see what i am doing wrong?

ALTER PROCEDURE [dbo].[usp_addStudents]

@name VARCHAR(20),
@lastname VARCHAR(20),
@roll INT,
@class VARCHAR(10)
AS

BEGIN TRY
   BEGIN TRANSACTION
INSERT INTO SchoolStudents(name,lastname,roll,class)VALUES(@name,@lastname,@roll,@class)
COMMIT
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
     ROLLBACK
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

我想通过存储过程在error_log表中填写错误描述的另一个代码.

My another code in which i want to fill error description in error_log table through store procedure.

ALTER PROCEDURE [dbo].[usp_addStudents]
@name VARCHAR(20),
@lastname VARCHAR(20),
@roll INT,
@class VARCHAR(10)
AS

BEGIN TRY
BEGIN TRANSACTION
INSERT INTO SchoolStudents(name,lastname,roll,class)VALUES(@name,@lastname,@roll,@class)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT INTO Error_log (logdate,source,errmsg,remarks)
  VALUES (getdate(),'usp_addStudents',ERROR_MESSAGE(),'dfdf')
END CATCH

推荐答案

SqlException 类将有助于解决给定的问题.要编写代码，其结构应为:

SqlException class will help to resolve the given issue. To write code, the structure would be:

try{
}
catch (SqlException ex){
if (ex.Errors.Count > 0) {        
   switch (ex.Errors[0].Number)        {            
     case 547: // Foreign Key violation                
       throw new InvalidOperationException("Some",ex);
       break;            
     case 2601: // Primary key violation                
       throw new DuplicateRecordException("Some", ex);
       break;            
    }
}

在 http://msdn.microsoft.com/en-us/library/上的参考system.data.sqlclient.sqlexception.aspx [ ^ ]

Reference at http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception.aspx[^]

这篇关于存储过程中的错误处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！