Raise an error that the C# can catch

Often, our program execute stored procedure to do some processes. In the event of the data is missing or violate the business rules, the stored procedure should stop the process and return a useful message to the caller.

In order for the C#/ASP.net to be able to catch the exception, you have to pass in the appropriate values into raiserror() function.

For example:

  raiserror('This error will be able to catch by C#/asp.net',
11, 
1)

Just beware that the second parameter should start from 11 or otherwise C#/ASP.net will not be able to catch it.

To capture the current error, please use the following code:

declare @err_msg nvarchar(max),
        @err_severity int,
        @err_state int

set @err_msg = error_message()
set @err_severity = error_severity()
set @err_state = error_state()
 
raiserror(@err_msg,@err_severity,@err_state)