Apply commit and rollback in Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Apply commit and rollback in Stored Procedure

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-07-29 : 23:07:47

I'm a newbie in creating Sql Server 2000 Stored Procedure. I've 2 update statement below,

update tblsubjectregistered set Code='GJ76' where ID='A78'
update tblsubjectstatus set ID='A78',Class='U77',Status='F' where Code='GJ76'

Can anyone show me the accurate technique, to put above 2 update statement in Stored Procedure that apply commit and rollback to make sure this statement is a Single Workable Unit.

Really need advice.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-29 : 23:25:19

Did you check books online, has many sample code there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 00:39:46

quote:
Originally posted by Delinda

I'm a newbie in creating Sql Server 2000 Stored Procedure. I've 2 update statement below,

update tblsubjectregistered set Code='GJ76' where ID='A78'
update tblsubjectstatus set ID='A78',Class='U77',Status='F' where Code='GJ76'

Can anyone show me the accurate technique, to put above 2 update statement in Stored Procedure that apply commit and rollback to make sure this statement is a Single Workable Unit.

Really need advice.

wrap them in a transaction

begin tran yourtesttran
...
set @ErrVar=@@error
IF @ErrVar >0
rollback tran yourtesttran
else
commit tran yourtesttran

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-07-30 : 02:11:31

i had try as follow,
CREATE PROCEDURE UpdUsrType @UsrID varchar(30), @UsrType varchar(30)
AS
BEGIN
Begin Tran UpdPDUsrType
-- T-SQL statement
UPDATE mUser SET
UsrType=@UsrType
where UsrID=@UsrID
UPDATE mUserType SET
UsrType=@UsrType
where RolesID=1
-- End T-SQL statement
set @ErrVar=@@error
IF @ErrVar>0
rollback tran UpdPDUsrType
else
commit tran UpdPDUsrType
END

this is an error,
Msg 137, Level 15, State 1, Procedure UpdUsrType, Line 18
Must declare the scalar variable "@ErrVar".
Msg 137, Level 15, State 2, Procedure UpdUsrType, Line 19
Must declare the scalar variable "@ErrVar".
Msg 156, Level 15, State 1, Procedure UpdUsrType, Line 21
Incorrect syntax near the keyword 'else'.
really need help.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-07-30 : 03:31:05

declare @ErrVar integer....up near the top of the code.

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-07-31 : 02:24:02

yup. it's settle now. tq.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources