Context

I’m not a big fan of handling exceptions in stored procedures. It means that you have complicated queries, thus have some business logic in them. Although I like to keep my logic in code, sometimes it’s necessary to have some logic in your stored procedure.

Usually, I only use it to increase performance.

How they did it

“They” tend to (ab)use stored procedures (SP) a lot. Taking most of the business logic and put them in SPs. Doing this confuses all developers that have to maintain these SPs. You’ve got SPs calling other SPs and eventually you hope that you’ll get your result.

Going even further away of best practices, take this method and add some extra parameters. This way we can implement an IF … ELSE IF … ELSE tree and only have to call one SP, which is able to give us completely different results. To makes our life easy? I don’t think so…

All the above brings us to exception handling and how they use it. Below I have a pseudo query and two c# examples how they handled the exception in code.

Example

The pseudo stored procedure:

BEGIN SP
	BEGIN IF @var = value1
		@result = SELECT x FROM tableX
	ELSE
		@result = SELECT y FROM tableY
	END IF

	BEGIN IF @result = z
		INSERT ‘Error msg’ in tableZ
		PRINT ‘Error msg’
		SELECT 1/0
	ELSE
		UPDATE tableQ SET q = @var2 WHERE qX = @result
	END IF
END SP

Pay attention to the ‘SELECT 1/0′. This is their way of exception handling in SQL. A nice thing to see is that they at least logged the error in a table.

Code example 1:

public DataSet GetData()
{
    try
    {
        SqlConnection conn = new SqlConnection("CONN_STRING");
        SqlCommand cmd = new SqlCommand("SP_NAME", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        //Add Parameter values
        cmd.ExecuteNonQuery();
        conn.Close();
    }
    catch (Exception)
    {
        //Maybe do something with it
        throw;
    }
}

Problem: When an error occurs, the Close() method will not be called. The thrown exception will not provide us with any useful information. So the only thing we know: an exception occurred.

Code example 2:

public DataSet GetData()
{
    DataSet result;
    try
    {
        SqlCommandHelper helper = new SqlCommandHelper();
        result = helper.GetResultFromSP("SP_NAME");
    }
    catch (Exception ex)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Error", "".GetType());
        dt.Columns.Add("ErrorDesc", "".GetType());
        dt.Rows.Add(dt.NewRow());
        dt.Rows[0][0] = "ShowError";
        dt.Rows[0][1] = "Error: "+ ex.Message.ToString().Replace("'", "");
        if(result == null)
            result = new DataSet();
        else
            result.Clear();
        result.Tables.Add(dt);
    }
    return result;
}

public void CallData()
{
    DataTable result = GetData().Tables[0];
    if(result.Rows.Count > 0 && result.Rows[0][0].ToString() == "ShowError")
        InsertLog(result.Rows[0][1].ToString());
}

I cried a little when I saw this. I cried even more when they told me that this was an excellent way of exception handling. Let’s recap: I don’t know what the actual error was (cf: 1/0), they hide the error in a DataTable. FYI, they also use this method with SELECT statements.

How I do it

Why don’t you just use the foreseen methods that Microsoft provided?! There are two methods you can use for decent exception handling in SQL:

  • RAISERROR
  • PRINT, equivalent of RAISERROR(message, 1, 1).

In short the RAISERROR method accepts 3 arguments: Message, Severity and State. If the Severity is higher than 10, it will throw a SqlException. If it’s below 10, it will send a warning message but won’t fail the procedure.

I wrote a small Console application to show the use of RAISERROR and PRINT.

CREATE PROCEDURE [dbo].[TestExceptionHandling]
AS
BEGIN
    PRINT 'I entered the SP'
    RAISERROR('I raised a warning', 10, 3)
    RAISERROR('I killed the SP!', 11, 7)
END
using System;
using System.Data.SqlClient;

namespace sqlserverexceptionhandling
{
    class Program
    {
        static void Main(string[] args)
        {

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(@"CONN_STRING");
                conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
                SqlCommand cmd = new SqlCommand("TestExceptionHandling", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                reader.Close();

                Console.WriteLine("No errors occured");
            }
            catch (SqlException ex)
            {
                Console.WriteLine("SQL exception: " + ex.Message + " (State: " + ex.State.ToString() + ")");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
            }
            finally
            {
                if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                    conn.Close();
            }

            Console.ReadKey();
        }

        static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            Console.WriteLine("SQL message received: " + e.Message + " (State: " + e.Errors[0].State.ToString() + ")");
        }
    }
}

Detailed info about the RAISERROR method: on MSDN