sql server passing error to application

Question

I need to pass the error to the application. I wrote the stored procedures as below: Does it pass the error to the application or we need to do the something more?

Eg.

create procedure insert_emp 
as
  begin try
       begin tran
         insert into emp values(..........)
       commit;
   end try

   begin catch
     if @@trancount > 0
     rollback
     declare @errMessage varchar(2000),
     declare @errNumber int
     select @errMessage = error_message(), @errNumber = error_number()
     raiserror(@errMessage, @errNumber,1)
   end catch

If I need to pass 0 if there is no error and a error number if there was a error, how to do it using raiserror? How can we log the error to error_table using raiserror? More importantly can above eg. pass the error to an application?

Answer 1

RAISERROR will pass the error to the client.

If you want to log the error in the database, do that first in the catch area, and then call raiserror.

If you do not want to pass the error to the client (I have worked at companies who prefer to return error codes) then instead of calling raiserror, you can simply return a non-zero value from the procedure, like this:

RETURN -1

Answer 2

Don't pass raiserror(@errMessage, @errNumber,1). The arguments for RAISERROR are message id, severity, state. You are passing @errNumber for severity, which is incorrect.

You should instead raise a custom error and pass the caught exception information in the error message as message format inserts:

RAISERROR(N'insert_emp: Error %i: %s', 16, 1, @errNumber, @errMessage);

You should use severity 16 for errors that you want to throw a SqlException in the client side processing (or other exception, according to the client access technology used).

You should use severity 1 for informational messages.