SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


GOTO and T-SQL


GOTO and T-SQL

Author
Message
batesview
batesview
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 422
Got to agree with the general point of Phil's editorial. Look at the instruction set of even the most up to date Processors and they all still include a GOTO and or JUMP statement. Sometimes there is just not a better way to skip some statements. In T-SQL oddly I've never needed GOTO but the day may come and I'm glad its still there.
Ian Massi
Ian Massi
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2315 Visits: 2190
First assignment in university I used GOTO.
It came back with red ink saying not to.
I cried and I wailed.
Then realized it was I who had failed.
Then I bid GOTO adieu.
Simon Facer
Simon Facer
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1741 Visits: 724
I've been coding for 25+ years, starting out in COBOL on IBM mainframes. GOTOs when used wisely can provide an invaluable coding construct. It all comes down to the 'style' of the coder - if they use GOTO carefully and with structure, it can really simplify a code module. But, unfortunately, most people just abuse it, and they (rightly so) get told not to do that again.
Like a lot of options, use it wisely, because if you abuse it, it will bite you.

And, YES, I do still code with GOTOs in T-SQL, where appropriate.



GSquared
GSquared
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24097 Visits: 9730
I'd use a GOTO if I felt the need. Mostly though, the "Not-a-GOTO" statements handle it these days. So I haven't used a literal "GOTO" in T-SQL in years.

After all, "While Begin End" is just a goto loop, with "End" instead of "Goto" and "While" instead of a target label. Same can be said for Try Catch being a "covert GOTO".

Different name, same mechanism, same use, better rep.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12485 Visits: 10691
In addition to making the code less structured and less readable, another problem (at least for those who choose to use it) with T-SQL's implementation of GOTO is that it's less functional; it's a one way jump and there is no equivalent to Visual Basic's RETURN statement to return control to the statement after the one that issued the GOTO. We do have a RETURN statement, but it's not used for returning from a GOTO branch. That limited control flow functionality was actually good, it prevented T-SQL developers from ever making extensive usage of the GOTO. I don't think I've ever worked in an organization where it's use was common, except for error handling in v2000 and earlier.

I use bit flag variables for control flow extensively, and I may even have a half dozen or more for a procedure with a complex braching process, because the same indicator may control multiple code blocks, and that reusability makes the code more readable. For example:

if @use_old_elig_table = 1
begin
...
end;

Sometimes I'll even expose the indicator flags as output parameters, so they can be used for unit testing purposes, or so the application can use them to determine details about what steps were performed internally by the stored procedure.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Randy Rabin
Randy Rabin
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 561
no equivalent to Visual Basic's RETURN statement to return control to the statement after the one that issued the GOTO


I think you're actually thinking of the GOSUB statement, which we have in T-SQL as EXEC.

While I haven't personally used GOTO in a very long time either, I can still see a restricted use for it, for the purpose of jumping forward in code (but never backward). Just my 2c but I don't always want every stored proc I write to be one huge TRY..CATCH block.

I find it interesting that Microsoft themselves still use GOTO (2008 R2 at least, haven't tried 2012) when scripting out a SQL Agent job from SSMS.



Revenant
Revenant
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7431 Visits: 4865
GOTO is not as evil as it is painted today. When Dijkstra wrote his paper - which not many today's programmers have read -, real programmers programmed in Assembler and GOTO was one of the flavors of the 'branch' instruction. The recommended error handling construct looked like this:


* coming back from a Supervisor call; return code is in register 1
* return codes were multiples of 4
B *+4(1) *+4 is the address of the following instruction
B success you get here if the return code was 0
B error4 you get here if the return code was 4
B error8 etc.

Imagine debugging a program with several 'branch tables' like this. Some Supervisor calls might return over 20 different errors.

On the other hand, some GOTOs were actually encouraged; e.g., BXLE or Branch on Index Lower or Equal, which was the best way to do a WHILE loop.
pdanes
pdanes
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 1354
Blanket prohibitions are generally a bad idea in any field. All processors have Jump instructions, which are low-level GOTOs, and high-level languages compile or interpret down to that, so your actual code is full of GOTOs anyway, now matter how fanatic you are about structure. They're like any tool - they can be misused, and they can be the best tool for the job. People who automatically just repeat the 'GOTO is BAD' mantra are sheep - I prefer to think for myself.

I also wish T-SQL had a GoSub statement - would have been perfect for a job just today. Instead, I wound up with a couple of GOTOs and some awkward tests.
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7857 Visits: 3290
A lot of ideas that are painted black are due to mis-interpretations.

Take Hungarian notation. Simonyi suggested its use to signal intent but people mis-interpretted this to signify type. Thats why we get tables beginning with tbl; due to that mis-interpretation.

You could argue that SQL2005/2008 schemas and namespaces are actually closer to expressing Simonyi's original idea.

LinkedIn Profile

Newbie on www.simple-talk.com
IceDread
IceDread
Mr or Mrs. 500
Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)

Group: General Forum Members
Points: 533 Visits: 1145
Revenant (12/22/2011)
GOTO is not as evil as it is painted today. When Dijkstra wrote his paper - which not many today's programmers have read -, real programmers programmed in Assembler and GOTO was one of the flavors of the 'branch' instruction. The recommended error handling construct looked like this:


* coming back from a Supervisor call; return code is in register 1
* return codes were multiples of 4
B *+4(1) *+4 is the address of the following instruction
B success you get here if the return code was 0
B error4 you get here if the return code was 4
B error8 etc.

Imagine debugging a program with several 'branch tables' like this. Some Supervisor calls might return over 20 different errors.

On the other hand, some GOTOs were actually encouraged; e.g., BXLE or Branch on Index Lower or Equal, which was the best way to do a WHILE loop.


Don't tell me your one of those that thinks assembler is real programing? I bet you don't from the way you write! Thou it's not entirely uncommon. Those goofs that likes to invent the wheel all over again and again..

No I still stand by that GOTOs in 99% of all cases are written because the programmer did not know better and that was how he did it 20 years ago.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search