Using Goto - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Using Goto

Author

Topic

rajeshr
Starting Member

1 Post

Posted - 2004-10-19 : 06:15:13

Hi

I have some stored procedures , within the procedures i have one transaction.
Within the begin transaction , we perform a lot of various calls to functions , other stored procedures etc
Well after each such call we check for any error and if there is a failure or an error we do a rollback

I was wondeing if i substiuted all these rollback statements with a label .. l_RaiseErr. And then under that label wrote a rollback would that seem like a good approach?

Actually am a little worried about using goto statements in my code..
Here is a small example of the same
<code>
CREATE procedure prc_temp_JobStep1 (@intOuput int output)

AS

declare @nprojectid as integer
declare @njobid as integer
declare @ErrCode as Integer

if exists(select njobid from dbo.pmo_job)
Select @intOuput = 0
Begin
BEGIN TRANSACTION
SET xact_abort ON
SELECT top 1 @nprojectid = nprojectid from dbo.pmo_temp order by njobid asc
SELECT top 1 @njobid = njobid from dbo.pmo_temp where nprojectid = @nprojectid order by djobdate desc

UPDATE dbo.pmo_temp set djobdate= getdate(), sFlag = 'Y' where njobid = @njobid

if @@ERROR <> 0
Goto l_RaiseErr

EXECUTE @ErrCode = dbo.prc_temp2 @nprojectid

if @@ERROR <> 0
Goto l_RaiseErr

if @ErrCode <> 0
Goto l_RaiseErr

EXECUTE @ErrCode = dbo.prc_temp3 @nprojectid, 1

if @@ERROR <> 0
Goto l_RaiseErr

if @ErrCode <> 0
Goto l_RaiseErr

COMMIT TRANSACTION
Return

l_RaiseErr:
Select @intOuput = -1
RAISERROR ('The Publish Job has failed at Step 1', 16, 1)
ROLLBACK TRANSACTION

End

</code>

Please do advise

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-10-19 : 06:30:19

No, it is fine and there is nothing wrong with using GOTO either...


BEGIN TRAN

some code here

if @@ERROR <> 0 goto QuitWithRollBack

some more code here

if @@ERROR <> 0 goto QuitWithRollBack

even more code

if @@ERROR <> 0 goto QuitWithRollBack

COMMIT TRAN
GOTO ExitPoint

QuitWithRollBack:
ROLLBACK TRAN

ExitPoint:
RETURN

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources