Unique key constraint violation not escalated to parent stored procedure

Question

I have a stored procedure which calls 2 nested sub procedures. For Example,

MyProc
  BEGIN
    BEGIN TRY
      BEGIN TRAN
        EXEC UpdateProc
        EXEC InsertProc
      COMMIT TRAN
    END TRY
    BEGIN CATCH
      ROLLBACK TRAN
      ;THROW
    END CATCH
  END

The insert stored procedure could insert a line with duplicate values violating a unique key constraint. I'm calling the main stored procedure from a .NET application. In case of unique key violation, my application is not receiving the correct exception. The exception received is 'Specified Cast is not valid'. I've tried adding SET XACT_ABORT ON but this did not correct the problem.

Answer 1

You are missing the error handling piece (i.e. TRY...CATCH). Please see the stored procedure template that I posted in the following DBA.StackExchange answer:

Are we required to handle Transaction in C# Code as well as in stored procedure

Answer 2

Did you try to catch SqlException in C# code?

Below is my example:

SQL Code:

DROP PROC IF EXISTS Proc1
GO
CREATE PROC Proc1
AS 
BEGIN
    RAISERROR('This is Proc1',0,1)
END
GO
DROP PROC IF EXISTS Proc2
GO
CREATE PROC Proc2
AS 
BEGIN
    RAISERROR('This is Proc2',18,1)
END
GO
BEGIN TRAN
    BEGIN TRY
        BEGIN TRAN
        EXEC Proc1
        EXEC Proc2
        COMMIT TRAN
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        ;THROW
    END CATCH
ROLLBACK
GO
--DROP PROC IF EXISTS Proc1
--GO
--DROP PROC IF EXISTS Proc2
--GO

.NET code:

...
try
{
    cnn.Open();
    SqlCommand command = new SqlCommand(@"BEGIN TRY
                                            BEGIN TRAN
                                                EXEC Proc2
                                                EXEC Proc1
                                            COMMIT TRAN
                                        END TRY
                                        BEGIN CATCH
                                            ROLLBACK TRAN
                                            ; THROW
                                         END CATCH", cnn);
    command.ExecuteNonQuery();

    cnn.Close();
}
catch (SqlException ex)
{
    Console.WriteLine(ex.Errors[0].Message);
}

RESULT: