CODEWITHSUNDEEP
c#exceptionado.netsqldatareader
Ananth
Ananth

Reputation: 10700

To get the Error_Number() from the stored procedure to the calling .NET application

Hi what is the best way to pass the Error_Number() from the stored procedure to the calling .NET application?

I know we can do it by setting an output parameter or through a SELECT query.

Note: I am using a data reader.

Thanks in advance.

Upvotes: 2

Views: 3083

Answers (2)

marc_s
marc_s

Reputation: 754518

You could try to do this - catch your Error_number() in a SQL try/catch block and then raise an error, which you can catch in your .NET application as a SqlException.

In your SQL stored procedure:

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;

and in your .NET code (here: 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);
    }
}

Upvotes: 4

Royi Namir
Royi Namir

Reputation: 148524

try this : 

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;

Upvotes: 0

Related Questions