Transaction Handling In SqlServer 2005


1) Create Table Which are Describe Below.

–-Table Structure


–TableName : Users

Field Name Data-types
UId int (primary key auto increment)
UserName varchar(50)
Password varchar(50)


–TableName : UserDetail

Field Name Data-types
UserDetailId int(primary key auto increment)
UserId int
Address1 varchar(50)
Address2 varchar(50)

2) Create Simple Store Procedure and put the below code in to that.

-–if any error occure in the query at that time we rollback whole transaction else commit


begin try

–-start transaction

begin transaction

insert into [Users]




–-get the last insert id (Uid)

–-select scope_identity()

insert into UserDetail




–-whole above transaction commit


end try

begin catch

–-whole above transaction rollback

print ‘error is come’


end catch

3) check the code.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s