ErrorHandling in SQL Server chained stored proc wi

Question

I am chaining together some stored procedures and have run into some issues in getting error handling working correctly. As some of these stored procedures are long running, I've made use of SqlInfoMessageEventHandler and code such as RAISERROR('whatever',10,1) WITH NOWAIT within the stored procedures to report the progress of the operation to the user. In order to do this, I've read that you can't use cmd.ExecuteNonQuery() and instead have to use cmd.ExecuteReader(). I've tried this, and it does seem to be the case; I don't see any messages from ExecuteNonQuery.

The problem I found though is that when I use ExecuteReader, if my stored proc throws any errors, then they are ignored. By this I mean my .NET Application that calls this stored proc from a try block and when the stored proc encounters an error (like SELECT 1/0), execution never enters the catch block, but instead commits my transaction. An example of this is as follows;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempTstTbl]') AND type in (N'U'))
DROP TABLE [dbo].[TempTstTbl]

CREATE TABLE [dbo].[TempTstTbl] (
    Step INT,
    Val  VARCHAR(50)
)

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Child]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[Child]
GO 
CREATE PROCEDURE [dbo].[Child]
AS
BEGIN
BEGIN TRY
    INSERT INTO [dbo].[TempTstTbl] (Step, Val) VALUES (1, 'FROM CHILD BEFORE FAULT')
    SELECT 1/0
    --RAISERROR ('This should really fail', 16, 2)
    INSERT INTO [dbo].[TempTstTbl] (Step, Val) VALUES (2, 'FROM CHILD AFTER FAULT')
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Parent]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[Parent]
GO
CREATE PROCEDURE [dbo].[Parent]
AS
BEGIN
BEGIN TRY
    INSERT INTO [dbo].[TempTstTbl] (Step, Val) VALUES (1, 'FROM PARENT BEFORE CHILD')
    Exec [dbo].[Child]
    INSERT INTO [dbo].[TempTstTbl] (Step, Val) VALUES (2, 'FROM PARENT AFTER CHILD')
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;
END
GO

EXEC [dbo].[Parent]
SELECT * FROM [dbo].[TempTstTbl]

With some .NET code such as;

private void button4_Click(object sender, EventArgs e)
{
    using (SqlConnection conn = new SqlConnection(@"Data Source=XPDEVVM\XPDEV;Initial Catalog=MyTest;Integrated Security=SSPI;"))
    {
        conn.Open();
        using (SqlTransaction trans = conn.BeginTransaction())
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.Transaction = trans;
                cmd.CommandText = "[cfg].[Parent]";
                cmd.CommandType = CommandType.StoredProcedure;

                try
                {
                    -- cmd.ExecuteReader(); -- Using this instead of ExecuteNonQuery means the divide by 0 error in the stored proc is ignored, and everything is committed :(
                    cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                }
            }
        }
    }
}

Does anyone have any ideas as to how I can get my progress messages from my stored proc, but still catch .NET exceptions if errors occur in the stored procedure?

Answer 1

It seems that this issue stems from the way DataReader works. I first came across a hint in Nelson Rothermel comment in this answer to a separate SO question.

I then further read details of this issue described on this thread where Richard McKinnon gives the following example as a way to address the issue (my emphasis);

Try the same with severity > 10 (I am using 11) and add the SELECT line to the SP (I checked using the NorthWind DB). You'll see that when you have a SELECT that retrieves data, you never get the exception.

[Original message clipped]

In this case ExecuteReader() will start process the first set of results, the SELECT statement. To get the next set of results, the RAISERROR, you need to call NextResult().

So to just look at the errors I would change my code from before to look like this;

SqlDataReader dr = command.ExecuteReader(); dr.NextResult(); dr.Close();

I tried adding dr.NextResult() to my code and it does seem to have fixed the issue for me.

This then allows me to get info messages from my stored proc, but also allows me to catch errors raised in the stored proc also.