SQL Server Raiserror not causing exception in .NET client

10,221

Solution 1

I went through the SQL Helper class and found out that ExecuteScalar eats the exception and returns null. I switched to ExecuteDataSet which doesn't do this. I expected the different Execute.. method to behave the same way. The other way is to use ExecuteScalar and when the SP detects an error, it does a SELECT some error number which can be handled in the client.

Solution 2

According to SQL Books online severity of 16 "Indicates general errors that can be corrected by the user." - so that severity is OK.

I only have SQL 2008 to work with, but I have tested the RAISERROR('some error message',16,1) and the error was caught in my c# app. Are you sure the error is not being handled in your "SqlHelper" class?

Share:
10,221
Stephanie Dente
Author by

Stephanie Dente

Updated on July 25, 2022

Comments

  • Stephanie Dente
    Stephanie Dente over 1 year

    I have a stored procedure on a SQL Server 2005 database which has a statement like this:

      IF @Condition = 0
        BEGIN
            RAISERROR('some error message',16,1)
            RETURN
        END
    

    and it's called from a C# client like so:

     try
               {
                    SomeVariable = SqlHelper.ExecuteScalar(GetConnectionString(), "MySP", new object[] { param1, param2});
                }
                catch (SqlException e)
                {
                    Console.WriteLine(e.Message);
                }
    

    However there's no exception being raised. The condition in the SP is always true for testing. To verify this, I copied the call from SQL Server Profiler and executed it in a query window and the ErrorMessage was printed which means the error is raised.

    Not sure what's happening.

  • Stephanie Dente
    Stephanie Dente over 14 years
    severity levels above 11 is an error which can be handled by the client.
  • Stephanie Dente
    Stephanie Dente over 14 years
    Thanks. I read your comment after I did my analysis which had the same conclusion.