Transaction Handling In SqlServer 2005

Steps:

1) Create Table Which are Describe Below.

–-Table Structure

–Table1

–TableName : Users

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

–Table2

–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

–-try

begin try

–-start transaction

begin transaction

insert into [Users]

(Username,Password)

values

(‘a’,‘a’)

–-get the last insert id (Uid)

–-select scope_identity()

insert into UserDetail

(UserId,Address1,Address2)

values

(‘a’,‘a’,‘a’)

–-whole above transaction commit

commit

end try

begin catch

–-whole above transaction rollback

print ‘error is come’

rollback

end catch

3) check the code.

Thnx

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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