How to capture error in SQL Server stored procedure? -

How to capture error in SQL Server stored procedure? -

i need capture error , send web application. example, below stored procedure:

create proc addname (@firstname varchar(10), @lastname varchar(10)) begin begin seek begin tran insert employee(firstname, lastname) values (@firstname, @lastname) end seek begin grab rollback tran print @@errormessage end grab end

here, how capture error , send web application? normally, best method handle stored procedure errors web application?

i using asp.net & c# front end end. need suggestions.

first, cannot rollback in begin catch. not without checking xact_state() first. unless xact_state() 1, cannot run rollback. think trivial case when exception 1205 (a deadlock has occurred) in case exception after transaction forcefully rolled back. see exception handling , nested transactions right pattern mixes transactions , error handling.

second, stored procedure not need transaction , error handling as is. unless real code much more complex, procedure doesn't add together value.

lastly, raise error utilize raiserror(). sql server 2012 has throw.

sql-server-2008 stored-procedures error-handling