Begin Transaction - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Begin Transaction

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2007-11-02 : 10:34:06

Could someone tell me if this is the correct way to begin and commit a transaction:

CREATE procedure AddTitle
@title varchar(255),
@descriptions varchar(255),
@mediaId int,
@quantityowned int,
@classificationid int

AS
BEGIN Tran TranStart

Set NOCOUNT ON
declare @titleid int --declaring the titleid

INSERT INTO Titles(title, [descriptions])
values(@title,
@descriptions)

set @titleid = SCOPE_IDENTITY() --grabbing the id to be placed in the other table

INSERT INTO Resources(titleid,mediaid,quantityowned)
values(@titleid,
@mediaid,
@quantityowned)

insert into titleclassification(titleid, classificationid)
values(@titleid, @classificationid)

select @titleid as titleid -- return to caller if needed

Commit Tran Transtart
GO

Where would I put rollback transaction? Do I need it?

Thanks!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-02 : 10:36:50

If it is SQL Server 2000, you need to check for error after each statement and in case of error, rollback the transaction.

For e.g.

INSERT INTO Titles(title, [descriptions])
values(@title, 
@descriptions)

If @@error<>0
Begin
Rollback Transaction
Return
End

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 10:41:38

also a check for when the transaction has already been rolled back

DECLARE @TRANCOUNT int
SET @TRANCOUNT = @@TRANCOUNT

BEGIN Tran TranStart

Set NOCOUNT ON
declare @titleid int --declaring the titleid

INSERT INTO Titles(title, [descriptions])
values(@title,
@descriptions)

set @titleid = SCOPE_IDENTITY() --grabbing the id to be placed in the other table

INSERT INTO Resources(titleid,mediaid,quantityowned)
values(@titleid,
@mediaid,
@quantityowned)

insert into titleclassification(titleid, classificationid)
values(@titleid, @classificationid)

select @titleid as titleid -- return to caller if needed

If @@error<>0
Begin
if @@TRANCOUNT > @TRANCOUNT
begin
Rollback Transaction
end
Return
End
Commit Tran Transtart

JJ297
Aged Yak Warrior

940 Posts

Posted - 2007-11-02 : 10:46:45

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources