none
Raiserror inside try/catch in procedure is not caught in sqlDataReader RRS feed

  • Question

  • Consider this example tables and procedures:

     

    CREATE TABLE T1 (col1 CHAR(1))

    CREATE TABLE T2 (col1 CHAR(1), col2 INT)

    INSERT INTO T1 VALUES ('A')

    INSERT INTO T1 VALUES ('B')

    INSERT INTO T2 VALUES ('A', 0)

    INSERT INTO T2 VALUES ('A', 4)

    INSERT INTO T2 VALUES ('B', 5)

    CREATE PROCEDURE errorProc

    AS

    select * from t1 inner join t2 t2a on t1.col1 = t2a.col1

    where t2a.col2 >=

    (select isnull(max(t2b.col2), 'xx') from t2 t2b where t2b.col1 = t1.col1 and t2b.col2 <= 3)

    GO

     

    Running this procedure return two records, but will fail on the third record with error message:

     

    Msg 245, Level 16, State 1, Procedure errorProc, Line 3

    Conversion failed when converting the varchar value 'xx' to data type int.

     

    If we in C# use sqldatareader and the read() method, we will read two records and the third will raise an exception.This is as expected. But if we change the procedure to contain try/catch and do a raiserror in the catch block, the sqldatareader.read() will not raise an exception. We have to do a sqldatareader.NextResult() to get the exception raised by the catch block.

     

    Can someone tell me why it is so. Is it a bug or is it by design?

     

     

    Tuesday, October 2, 2007 7:53 AM

All replies

  • Hi,

     

    Can you please post the code of your function (procedure) in which you are implementing your try/catch? This would speeden up the process to help you with your problem.

     

     

     

    cheers,

     

    Paul June A. Domag

    Tuesday, October 2, 2007 3:21 PM
  • In our debugging process we have tried several variants, from the simplest that just do a raiserror to more sofisticated ones. Here are the procedure with our "standard" catch block:

     

    CREATE PROCEDURE errorProc

    AS

    BEGIN TRY

    select *

    from t1

    inner join t2 t2a on t1.col1 = t2a.col1

    where t2a.col2 >=

    (select isnull(max(t2b.col2), 'xx') from t2 t2b where t2b.col1 = t1.col1 and t2b.col2 <= 4)

    end try

    BEGIN CATCH

    DECLARE @msg NVARCHAR(4000)

    DECLARE @procedure NVARCHAR(100)

    DECLARE @message NVARCHAR(4000)

    DECLARE @severity INT

    DECLARE @errorstate INT

    DECLARE @line INT

    SELECT @severity = ERROR_SEVERITY(),

    @errorstate = ERROR_STATE(),

    @procedure = ERROR_PROCEDURE(),

    @message = ERROR_MESSAGE(),

    @line = ERROR_LINE()

    SET @msg =

    N'<ERROR SOURCE=%s>

    <MESSAGE>%s</MESSAGE>

    <NUMBER>%i</NUMBER>

    <SEVERITY>%i</SEVERITY>

    <STATE>%i</STATE>

    <LINE>%i</LINE>

    </ERROR>'

    RAISERROR(@msg, @severity, @errorstate, @procedure, @message, @severity, @errorstate, @line);

    END CATCH

    GO

     

    We have tried to return different @severity codes, in all ranges (1-10, 11-19, 20-)

    Wednesday, October 3, 2007 4:51 AM
  • Hi,

     

    When you say NextResult, it will move to the next record and only then exception will be raised if any, it is by design. I could not understand why are you trying xx when you know datatype is integer, you should have tried 0. If you want to interpret if it is null then you can use CASE after you get result.

     

    Hope this will help.

     

    Wednesday, October 3, 2007 6:12 AM
  • Thank you for your interest in my problem.

     

    My point is that we should get the error when reading the first (and only) result set. NextResult() moves to the next result set according to the documentation. My procedure returns only one result set, so I don't think this is very intuitive.

     

    The procedure is only for debugging purposes. I should have created a more "real life" example. Any error would have caused this problem. The point is that the procedure must return at least one record before failing.

     

    Here is the C# code for reading the result set:

     

    ...

    using (System.Data.SqlClient.SqlDataReader reader = commandError.ExecuteReader())

    {

    if (reader.HasRows)

    {

    while (reader.Read())

    {

    Debug.WriteLine("Record found");

    }

    }

    reader.NextResult();

    }

    ...

     

    When not using the try/catch in the procedure this code works fine without the reader.NextResult(). 

    Wednesday, October 3, 2007 6:47 AM
  • Hi,

     

    Now I understand your problem better. I cam across this one in past also. Here is the link you can find solution.

     

    http://www.dotnet247.com/247reference/msgs/29/147987.aspx

     

    and another thread on same issue

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2202397&SiteID=1

     

    Try these out, should work else revert.

     

    Cheers

     

    Wednesday, October 3, 2007 8:43 AM
  • Thanks again for your interest. The links gave me some information, but did not give an exact answer why the best practice for try/catch in sql server 2005 procedures does not throw an exception to the data layer of my code.

     

    I think this is strange. I also think it is strange that we are the only ones experiencing this problem. As far as we know, we are using best practice both for error handling in stored procedures and for coding the data layer in C#.

     

     

    Friday, October 12, 2007 8:03 AM
  • I know this is VERY old, but this is still an issue. I too find this to be very odd behavior and it is amazing that Microsoft developed it like that. It makes for using try/catch/raiserror/throw in sprocs a non-solution, because they end up putting the exception in a way only accessibly via calling NextResult(), which is counter-intuitive. The sproc should have either worked (produced results), or failed (produced an exception), not a mix-and-match!

    Please remember to mark any helpful replies as such

    Friday, February 26, 2016 8:25 PM