Exception handling in store procedure in sql server 2005

Exception handling is used to handle the errors occurred in program. If any problem occurred then a specific action should be completed, it is called exception handling. Usually in .net we use try catch statements. similar to this if we want to handle exception in sql server store procedure then we will use begin try and end try.
Please read example given below :

Create proc dbo.insertUser
(

@username
varchar(50),

@createdon
datetime,

@salary
varchar(10)

)

as
begin
-----------------------
begin try -- from here exception handling start  here
-----------------------
begin transaction
set IDENTITY_INSERT tbl_emp ON
insert into tbl_emp( username, createdon, salary)values
( @username, @createdon, @salary)
commit transaction

-------------------

end try -- try block ended

-------------------

begin catch -- if an error occurred the this block will execute
rollback transaction -- Transaction will be roll back
insert into tbl_error (logDate,source,msg)values 
(getdate(),'insertUser',error_message()) -- error description will inserted in tbl_error
end catch
end

We are using two tables in this tbl_emp and tbl_error. We are inserting an entry in tbl_emp. If execute complete then entry will be successful inserted other wise error decription will be inserted in tbl_error.

Click here to go on website