1

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.

0

2 Answers 2

2

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

2
  • Thanks for your reply.Sorry, I'm missed in the TRY-CATCH block in my question. I did used in my stored procedure. I have even tried your stored procedure template, the error is still not sent to my .net application
    – smmniceguy
    Jun 16, 2016 at 9:58
  • @smmniceguy Did you apply the template to all 3 Stored Procedures? If so, then you might need to show some of that code. Are you sure that your .NET exception handling is being done correctly? The Specified Cast is not valid message could be suggesting that there is a secondary problem in the .NET code. So you might need to show that as well. Jun 16, 2016 at 20:00
0

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:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.