GOTO @Value - 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 @Value

Author

Topic

malawneh
Starting Member

24 Posts

Posted - 2007-04-11 : 15:24:00

Is it possible to have a GOTO statement go to a variable value?

ie.

DECLARE @SaveStepNumber INT
, @StepNumber VarChar(10)
SELECT @SaveStepNumber = 1005

-- Chose process step
SELECT @StepNumber = s + CONVERT(VarChar,@SaveStepNumber)
GOTO @StepNumber

--Step 1000
s1000:
...SQL Code for Step

Michael Alawneh, DBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 15:33:28

No, I don't think so.
And... If you need it, you might be in trouble. There has to be other ways to solve your dilemma.

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-11 : 15:42:49

Why would you replace

DECLARE @SaveStepNumber INT
, @StepNumber VarChar(10)
SELECT @SaveStepNumber = 1005

With

GOTO 1005

????

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

malawneh
Starting Member

24 Posts

Posted - 2007-04-11 : 15:50:59

This is for an SP that returns values back to the user. They are allowed to make decissions on the fly to continue. But in order to not return to the same step that exited them there is a step logic in place.
So they run to a point they get a business logic question, the app ask do you want to continue sort of thing? they respond "yes". upon return I use a variable to point them to the correct starting spot before so they do not have to return to the sp at the begining again and have the same question pending. They will continue at the next step from where they left off.

Michael Alawneh, DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-11 : 15:54:10

You have a flawed design then. Your application should be handling this kind of logic, not the stored procedure.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 15:56:58

SQL Server 2005 is a set-based database management system.
What you are trying to achieve is best solved with a programming language like C#, VB, VB.Net or similar.

Put some massive number of IF's there. You already must know the step numbers, right?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 15:57:59

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-11 : 15:58:21

Even in a "real" programming language, in general GOTO's are bad ideas.

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-11 : 16:58:40

Nope, the label has to be an identifier, so you cannot use a variable.

IF @Step = '1001' GOTO s1001
IF @Step = '1002' GOTO s1002
IF @Step = '1003' GOTO s1003
IF @Step = '1004' GOTO s1004
IF @Step = '1005' GOTO s1005

malawneh
Starting Member

24 Posts

Posted - 2007-04-11 : 17:42:09

Thank you all.

I am actually going to abondon this GOTO approach for something more like this.

DECLARE @SaveStepNumber INT
, @StepNumber VarChar(10)
SELECT @SaveStepNumber = 1005

--Step 1000
IF @SaveStepNumber = 1000
BEGIN
sqlCode
increment @SaveStepNumber = @SaveStepNumber + 1
END

IF @SaveStepNumber = 1001
BEGIN
sqlCode
increment @SaveStepNumber = @SaveStepNumber + 1
END

etc...

Michael Alawneh, DBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 17:49:12

???
All steps above and including 1005 will be executed anyway!

DECLARE @StepNumber INT
SELECT @StepNumber = 1005

--Step 1000
IF @StepNumber <= 1000
BEGIN
sqlCode
END

IF @StepNumber <= 1001
BEGIN
sqlCode
END

IF @StepNumber <= 1002
BEGIN
sqlCode
END

IF @StepNumber <= 1003
BEGIN
sqlCode
END

IF @StepNumber <= 1004
BEGIN
sqlCode
END

IF @StepNumber <= 1005
BEGIN
sqlCode
END

IF @StepNumber <= 1006
BEGIN
sqlCode
END

Peter Larsson
Helsingborg, Sweden

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-04-11 : 19:11:01

seems to me that this is an appropriate time for "helper" SPs

IF @var = 1
Exec sp_1

IF @var = 2
Exec sp_2

...

much better chance of having a decent query plan cached this way for the individual steps

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-04-11 : 22:53:42

Sweet. That's the most perverted thing I've seen in a long time. Thanks!
Russell - I was going to suggest that too but from the front end. Not sure what benefit having the conditions in the sp gives (although there might be some).
This is a variation of the anti-pattern where someone does a for loop, then a case statement for each value of the loop.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-04-11 : 23:47:47

i cant think of any advantage, but i'll tell u that i've seen some crazy SPs where they did

IF @var = x
--do insert
IF @var = y
--do update
IF @var = z
-- do delete

or maybe selecting from completely different tables.

I agree that the logic belongs in the middle-tier/front end, but in case where SP already exists that performs different actions, and performance is bad (because no query plan can be cached), then DBA can modify to execute individual SPs. Or sometimes it could be a 3rd party app where u dont have source code. But you are right, and in this case seems like OP wants to create the proc, so probably not too late to put the logic where it belongs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources