ExecuteReader not throwing exception from T-SQL when using TRY-CATCH in stored proc

[Adam Gabai]

I am having an issue with SqlCommand class in C#. When I use ExecuteReader() on a T-SQL stored procedure that calls
RAISERROR() in a catch block the error does not get thrown in C#. I searched online and didn't find much but the one solution that I found that is pretty akward is to call NextResult() after done reading from tables.

Here is a simple example of what my SP is doing:

CREATE PROCEDURE [dbo].[test] 
AS
BEGIN
 
    DECLARE @ErrorMessage    NVARCHAR(4000);
    DECLARE @ErrorSeverity    INT;
    DECLARE @ErrorState        INT;
 
    BEGIN TRY
 
        select 1 / 0
 
    END TRY
 
 
    BEGIN CATCH
 
        SET @ErrorMessage = ERROR_MESSAGE();
        SET @ErrorSeverity = ERROR_SEVERITY();
        SET @ErrorState = ERROR_STATE();
        RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
 
    END CATCH
END

If you use ExecuteNonQuery() then the exception will come up but if you use ExecuteReader() it will not come up until you call NextResult().

Thanks,
Adam

[Curtis Rutland]

It's because the error is part of a separate record set than the result.

Notice in SSMS when you do this query:

First it says (0 row(s) affected), then on the next line it shows the error.

If you look in the "Results" tab, you'll see there's actually an empty recordset as the first result:

[Adam Gabai]

I thought that but that doesnt make sense to me if I am calling it like this, also there is no result set I do not know what the 0 count is from

            try
            {
                connection.Open();
 
                SqlDataReader reader = command.ExecuteReader();
 
                while (reader.Read())
                {
                    Console.WriteLine("Line Read");
                }
 
                Console.WriteLine("After Read");
 
                // while (reader.NextResult()) ;
 
                Console.WriteLine("No Exception");
            }
            catch (Exception exception)
            {
                Console.WriteLine("Exception: {0}", exception);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

Also I cannot view the images you posted

[Curtis Rutland]

I don't know what to tell you, other than that's just the way it is. The exception isn't in the first record set. It's in the second. The reason for that is because it's possible to return both records and an exception. It doesn't change the way it works because there are no rows, it just uses an empty record set.

You'll have to change the way you call it. First check to see if the record set is empty, then check to see if it has a second. If it does, an exception will be thrown, and you can catch and handle it.

As to the images, I don't know why you wouldn't be able to see them. They're hosted on imgur.com, perhaps that's blocked for you.

[Bassem]

Hi, I'm not expert with SQL, but I've a theory - That I tested:

The row that appears in the result is because of the select in:

select 1 / 0

To avoid that, do the calculation then select the result like this:

BEGIN
 
    DECLARE @ErrorMessage    NVARCHAR(4000);
    DECLARE @ErrorSeverity    INT;
    DECLARE @ErrorState        INT;
 
    DECLARE @res int;
 
    BEGIN TRY
 
        --select 1 / 0
        set @res = 1 / 0
        select @res
 
    END TRY
 
 
    BEGIN CATCH
 
        SET @ErrorMessage = ERROR_MESSAGE();
        SET @ErrorSeverity = ERROR_SEVERITY();
        SET @ErrorState = ERROR_STATE();
        RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
 
    END CATCH
END

I hope this will help.

[Adam Gabai]

The bigger issue is that the exception is not getting registered. How do I make it so I will always have the exception thrown even if results are returned?

[Adam Gabai]

I have a stored procedure where I return a set of results and then do some updates. If the updates fail the C# SQLCommand.ExecuteReader() does not register the error. If I use NextResult() then the exception is trown like it was in the next set of results or something, though there is no other set. I can always call NextResult() and check or I can make sure the results are returned after the update, but is ther a safer way to flush out any possible errors that happen in stored procedure?

ex: This is the basic idea of what would happen with an error

SQL:
SELECT 1;
DECLARE @A int;
SET @A = 1 / 0;
 
C#:
SqlDataReader reader = command.ExecuteReader()
while(reader.Read())
{}
// No exception yet
// Now exception occurs with next line
reader.NextResult();
 

SQL Server 2008 - .Net 2.0