Throw an error in MSSQL and catch by C#

Published on: 5th Jun 2012

Updated on: 17th May 2021

Overview

Upon user submitting their data through a C# program, the program will execute the stored procedure and process the data. In the event of violating any business rules, the stored procedure should stop the process and return a message to C# and C# display the message on the screen.

How to throw exception in MSSQL

Here's how you throw exceptions in MSSQL.

raiserror('This is error to be catch by C#', 11, 1)

Where

• The first parameter is the validation or error message.
• The second parameter is the severity. Just beware that this value should start from 11 or otherwise C#/ASP.net will not be able to catch it.
• The third parameter is the error state.

Then, C# will be able to catch the error

try
{
    using (SqlConnection cn = new SqlConnection())
    {
        cn.Open();

        using (var cmd = cn.CreateCommand())
        {
            cmd.CommandText = "exec pr_some_stored_proc";
            cmd.ExecuteNonQuery();
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.