Handling error in SQL Server chained stored proc with ExecuteReader

Question

I am combining some stored procedures and encountering some problems with error handling correctly. Since some of these stored procedures take a long time, I also used SqlInfoMessageEventHandlercode, for example RAISERROR('whatever',10,1) WITH NOWAIT, in stored procedures, to inform the user about the progress of the operation. To do this, I read that you cannot use cmd.ExecuteNonQuery () and should use cmd.ExecuteReader () instead. I tried this, and it seems to be so; I do not see messages from ExecuteNonQuery.

The problem that I discovered is that when I use ExecuteReader, if my stored proc causes any errors, then they are ignored. By this I mean my .NET application that calls this stored process from the try block, and when the stored proc detects an error (e.g. SELECT 1/0), execution never enters the catch block, but instead commits a transaction. An example of this is the following:

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, for example:

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 on how I can get my progress messages from my saved proc, but still catch .NET exceptions if errors occur in the stored procedure?

Answer 1

, , DataReader. SO.

, , ( );

  

> 10 ( 11) SELECT SP ( NorthWind DB).      SELECT, ,     .

  

[ ]

ExecuteReader() , SELECT. , RAISERROR, NextResult().

, :

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

dr.NextResult() , , .

proc, , proc.