how to use goto 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)

how to use goto in stored procedure

Author

Topic

salmonraju
Yak Posting Veteran

54 Posts

Posted - 2006-11-01 : 05:20:05

i wrote a simple stored procedure

create proc mygoto
@sub1 varchar(6),@sub2 varchar(6),@sub3 varchar(6)
as
if(@sub1='fail')
goto label
if(@sub2='fail')
goto label
if(@sub3='fail')
goto label
else
goto label2
label:print 'the total result is fail'
return
label2:print 'the result is pass'

here my program is working well
BUT
IS THERE ANY PROBLEM IF I USE GOTO
WHY I AM ASKING IS IN SOME C-LANGUAGE BOOKS
AUTHORS TELLS NOT TO USE GOTO IN MOST OF THE TIME
(they prefer function )
May i use GOTO IN THIS SITUATION

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-01 : 05:50:03

I dont know the specific disadvantage in terms of performance, but i had read some where that using many of GOTO's label it create very difficult to read the code.

but why do you want to use GoTo Labels? from the example which you had posted i dont see any use of it..??

Chirag

http://chirikworld.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-01 : 10:19:45

Dont try to simulate front end code at sql
Use

IF ......
Begin
do what you want
End
ELSE IF
Begin
do what you want
End
ELSE IF
Begin
do what you want
End

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-01 : 10:50:59

I only use GOTO to handle errors and then exit to common error handling section

IF @Sub1 = 'FAIL' OR @Sub2 = 'Fail' OR @Sub3 = 'Fail' PRINT 'Fail' ELSE PRINT 'Pass'

Is all you need there

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-01 : 16:46:12

I just wish they allowed this:

GOTO @label_name

That would make things fun.

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-06 : 10:10:36

quote:
Originally posted by Michael Valentine Jones

I just wish they allowed this:
GOTO @label_name

That would make things fun.

CODO ERGO SUM

You funny

But you could do that with dynamic SQL I bet

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-06 : 10:29:45

for your logic instead of goto's use a bit variable which you set accordingly to success or failure:
declare @success bit
select @success = 1
if(@sub1='fail')
select @success = 0
if(@sub2='fail')
select @success = 0
if(@sub3='fail')
select @success = 0
if @success = 0
print 'the total result is fail'
else
print 'the result is pass'
return

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources