从存储过程获取Error_Number（）到调用.NET应用程序

Question

您好，将Error_Number()从存储过程传递到调用.NET应用程序的最佳方式是什么？

我知道我们可以通过设置输出参数或通过SELECT查询来完成。

注意：我正在使用数据读取器。

在此先感谢。

Answer 1

你可以尝试这样做 - 赶上你Error_number()在SQL try/catch块，然后产生一个错误，你可以在一个SqlException .NET应用程序赶上。

在SQL存储过程：

BEGIN TRY 
    -- do something here.... 
END TRY 
BEGIN CATCH 
    DECLARE @ErrorNumber INT 

    SELECT @ErrorNumber = ERROR_NUMBER() 

    RAISERROR 
(N'The error code is: %d', 
    16, -- Severity. 
     1, -- State. 
    @ErrorNumber,  -- First substitution argument. 
    '');    -- Second substitution argument. 
END CATCH; 

和.NET代码（位置：C＃）：

// call your stored proc some way... 
using(SqlConnection conn = new SqlConnection("server=.;database=Test;Integrated Security=SSPI;")) 
using (SqlCommand cmd = new SqlCommand("dbo.YourStoredProcNameHere", conn)) 
{ 
    cmd.CommandType = CommandType.StoredProcedure; 
    // possibly add parameters to the stored procedure call.... 

    try 
    { 
     conn.Open(); 
     cmd.ExecuteNonQuery(); 
     conn.Close(); 
    } 
    catch (SqlException ex) 
    { 
     // catch SqlException - ex.ErrorNumber contains your error number 
     string msg = string.Format("Error number: {0}/Message: {1}", ex.Number, ex.Message); 
    } 
} 

Answer 2

试试这个：

BEGIN TRY 
    -- Generate a constraint violation error. 

END TRY 
BEGIN CATCH 
    SELECT 
     ERROR_NUMBER() AS ErrorNumber 
     ,ERROR_SEVERITY() AS ErrorSeverity 
     ,ERROR_STATE() AS ErrorState 
     ,ERROR_PROCEDURE() AS ErrorProcedure 
     ,ERROR_LINE() AS ErrorLine 
     ,ERROR_MESSAGE() AS ErrorMessage; 


END CATCH;