Use of Exception Handling in SQL Server.

We can use TRY...CATCH block to handle Exception handling in SQL Server

For Example we have two table tbl_userinfo and tbl_login. We want that when a user register then create his/her login means we want entry in both table.

Suppose user register and values inserted in tbl_userinfo but not inserted in tbl_login in that case we want rollback values from tbl_userinfo also.So

begin try
begin tran userreg
declare @userid int
insert into tbl_userinfo(name, email)
values('sandeep','[email protected]')
select @userid=max(user_id) from tbl_userinfo
insert into tbl_login(login_id, user_id, user_name, password)
values(2,@userid,'singh','singh')
end try
begin catch
rollback tran userreg
end catch

http://geekswithblogs.net/madhawa/archive/2008/08/25/124661.aspx