How to throw an SQL Exception back to the calling asp.net application?

ado.net, asp.net, c#, sql, sql-server

Solution

The severity level of a `RAISERROR()` call in your stored procedure must be greater than or equal to 16 in order to generate a `SqlException` on the calling side. Here is an example of a `RAISERROR()` that will generate a `SqlException`:

RAISERROR ('Error raised in TRY block.', -- Message text.
           16, -- Severity.
           1 -- State.
           );

Problem

I currently have an ASP.NET application that handles all calls to the database through stored procedures and we handle all SQL transactions application side. My problem is that I need to throw an exception in my Stored Procedure so it can be caught in the Application and the transaction can be rolled back. I have currently tried RAISERROR and THROW but they only return messages and don't actual cause an exception. Any help would be appreciated. Thanks

Original source