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 2005 Forums

Transact-SQL (2005)

GOTO....

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-20 : 10:36:49

Just trying to build a SP that will function like the following:


CREATE PROCEDURE Test
@Selection int
AS
BEGIN
  IF @Selection = 1 GOTO ONE
  IF @Selection = 2 GOTO TWO
END
ONE:
Select * from test
TWO:
Select * from test1

I want to than call the SP by doing either Test '1' or Test '2', problem I have right now is when I call Test '1' it always executes Test '2' in the result set, how do I get it to exit after Test '1'? Or is this now a good way to be doing this???

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-01-20 : 10:45:18

quote:
Originally posted by duhaas

Just trying to build a SP that will function like the following:

CREATE PROCEDURE Test
@Selection int
AS
BEGIN
IF @Selection = 1 GOTO ONE
IF @Selection = 2 GOTO TWO
END
ONE:
Select * from test
return
TWO:
Select * from test1

I want to than call the SP by doing either Test '1' or Test '2', problem I have right now is when I call Test '1' it always executes Test '2' in the result set, how do I get it to exit after Test '1'? Or is this now a good way to be doing this???

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-20 : 11:06:50

ughhhh goto statements..

CREATE PROCEDURE Test
@Selection int
AS
BEGIN
IF @Selection = 1
Select * from test
ELSE
IF @Selection = 2
Select * from test1
END

A true programmer was once told to go to hell.. The programmer was more offended by the goto statement than the destination ; )

r&r

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-20 : 11:13:39

quote:
Originally posted by revdnrdy

ughhhh goto statements..

CREATE PROCEDURE Test
@Selection int
AS
BEGIN
IF @Selection = 1
Select * from test
ELSE
IF @Selection = 2
Select * from test1
END

A true programmer was once told to go to hell.. The programmer was more offended by the goto statement than the destination ; )

r&r

Appreciate the advice, so are you saying that this is the better route? Also is I just want to know if selection 1 is ran, it will not go to selection 2 correct?

bjoerns
Posting Yak Master

154 Posts

Posted - 2009-01-20 : 11:13:52

Nice one, revdnrdy :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 11:28:55

quote:
Originally posted by duhaas

quote:
Originally posted by revdnrdy

ughhhh goto statements..

CREATE PROCEDURE Test
@Selection int
AS
BEGIN
IF @Selection = 1
Select * from test
ELSE
IF @Selection = 2
Select * from test1
END

A true programmer was once told to go to hell.. The programmer was more offended by the goto statement than the destination ; )

r&r

Appreciate the advice, so are you saying that this is the better route? Also is I just want to know if selection 1 is ran, it will not go to selection 2 correct?

yup. using goto is not a good programming practise.
if selection 1 is ran, other condition wont run as it is inside else

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-20 : 11:33:23

And I assume I can keep adding more statements using else? Meaning, right now the example shows two, but I have more.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 11:37:29

quote:
Originally posted by duhaas

And I assume I can keep adding more statements using else? Meaning, right now the example shows two, but I have more.

yup you can nest if statements

IF condition1
....
ELSE IF condn2
...
ELSE IF..
ELSE IF..
ELSE IF..
ELSE
....

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-20 : 11:42:23

Thanks for your time and help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 11:45:34

welcome

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-20 : 11:50:16

Also note that if you want more than 1 statement to execute you need to include a BEGIN and END block..

As follows:

IF @statement=1
BEGIN
PRINT 'HELLO WORLD'
SELECT * FROM MyTable
PRINT 'DONE!'
END

ELSE
IF @statement=2
PRINT 'GOODBYE CRUEL WORLD!'

The first if condition uses multiple statements so it has to be encapsulated in a BEGIN END block. The second if condition only executes 1 statement so you don't need it.

cheers !

r&r

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources