How can i catch error in C# from RAISERROR of sql server?

Question

Here is my stored procedure code called "uspTest"

BEGIN TRY
SELECT 3 / 0;
END TRY

BEGIN CATCH
RAISERROR('D', 16, 3);
END CATCH

And Here is my c# code in visual studio

    public IHttpActionResult RegisterUser(RegisterModelView registerViewModel)
    {

        try
        {
            using (AusHerbEntities ctx = new AusHerbEntities())
            {
                ctx.uspTest();  
                Console.Write("i am in try block");
            }


        }

        catch (Exception e)
        {
            Console.Write(e.Message);

        }
        return Ok();
    }

When I run this code, the Catch block in C# code is not called, but I expect it to be called because the Stored Procedure raises error.

How can I get this code work?

I just want to do RAISERROR in catch block in Stored Procedure and catch the error in c# code.

Your code appears to be doing what you think it should. Have you put in break points to see what happens? — Sean Lange, Nov 16 '16 at 03:58
I can confirm too, the syntax in sql is looking correct. You can check this question I'm not aware if this is your problem: http://stackoverflow.com/questions/33502318/sqlexception-not-caught-in-winforms — mybirthname, Nov 16 '16 at 04:00
I just tried again but it does not work, the tread for executing Stored Procedure has expired with 0 which means that no exception is thrown? — kisung Tae, Nov 16 '16 at 04:41
Does it raise error if you put only `RAISERROR('D', 16, 3);` in your `uspTest`? — ydoow, Nov 16 '16 at 05:39
yep i think so I just removed the try and catch block in procedure and put only RAISERROR , then the catch block in C# is called — kisung Tae, Nov 16 '16 at 05:45
Just ran into this issue. I can confirm this [answer](http://stackoverflow.com/a/22126920) worked for me. — Suri, Apr 14 '17 at 00:01
What is the point of using `RAISEERROR` in the `CATCH` block? Use `THROW` if you want to throw an exception, or don't use a `CATCH` at all to let the exception reach the client. Assuming you perform some error-handling or logging logic first, `THROW;` will rethrow the original exception — Panagiotis Kanavos, Jul 07 '17 at 15:19

score 0 · Answer 1 · answered Nov 16 '16 at 05:44

0

To make SqlException thrown, the stored procedure must return with @@error set. In your case, END CATCH statement clears @@error. Inserting a RETURN statement immediately after the RAISERROR statement should make SqlException thrown.

BEGIN TRY
SELECT 3 / 0;
END TRY

BEGIN CATCH
RAISERROR('D', 16, 3);
RETURN;
END CATCH

answered Nov 16 '16 at 05:44

ydoow

2,969
4
24
40

I have tried but sorry mate it does not work, the catch block is not still called – kisung Tae Nov 16 '16 at 05:54
If i put RAISERROR('d', 16, 3) in the TRY block, it works fine but if I put SELECT 3 / 0; it does not work. It is really strange cause 3/0 also throws error right? – kisung Tae Nov 16 '16 at 07:34
Where there's an exception there's a THROW. Not RAISERROR – Panagiotis Kanavos Jul 07 '17 at 15:20

score 0 · Answer 2 · answered Jul 07 '17 at 01:27

Do you need the try-catch in SQL? If you're only using it to rollback the transaction, then you can simply set XACT_ABORT ON and dispense with the try-catch. The transaction will be rolled back, and any error will bubble through to the client. Here is a good article on the subject: https://www.simple-talk.com/sql/t-sql-programming/defensive-error-handling/

How can i catch error in C# from RAISERROR of sql server?

2 Answers2