SQLServerCentral / Editorials / SQLServerCentral.com / GOTO and T-SQL / Latest Posts

RE: GOTO and T-SQL

Revenant — Thu, 22 Dec 2011 19:17:57 GMT

[quote][b]IceDread (12/22/2011)[/b][hr]. . . Don't tell me your one of those that thinks assembler is real programing? I bet you don't from the way you write! . . .[/quote]No, I do not, not any longer; however, there was time when the only REAL programming was in Assembler. Those were the times when an hour of IBM /360 was $300, in 1970 dollars, and that was about programmer's monthly salary. COBOL compile could take say 15 minutes, assembly of ~3,000 lines of code less than a minute.Pick your heroes. Retroactively. ;-)

RE: GOTO and T-SQL

IceDread — Thu, 22 Dec 2011 17:10:40 GMT

[quote][b]Revenant (12/22/2011)[/b][hr]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:[code="plain"]* 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.[/code]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.[/quote]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.

RE: GOTO and T-SQL

David.Poole — Thu, 22 Dec 2011 16:36:59 GMT

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.

RE: GOTO and T-SQL

pdanes — Thu, 22 Dec 2011 15:48:14 GMT

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.

RE: GOTO and T-SQL

Revenant — Thu, 22 Dec 2011 11:13:31 GMT

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:[code="plain"]* 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.[/code]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.

RE: GOTO and T-SQL

Randy Rabin — Thu, 22 Dec 2011 10:59:52 GMT

[quote]no equivalent to Visual Basic's RETURN statement to return control to the statement after the one that issued the GOTO[/quote]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 [b]forward[/b] 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.

RE: GOTO and T-SQL

Eric M Russell — Thu, 22 Dec 2011 07:48:04 GMT

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 = 1begin ...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.

RE: GOTO and T-SQL

GSquared — Thu, 22 Dec 2011 07:11:20 GMT

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.

RE: GOTO and T-SQL

Simon Facer — Thu, 22 Dec 2011 07:07:15 GMT

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, [b]where appropriate[/b].

RE: GOTO and T-SQL

Ian Massi — Thu, 22 Dec 2011 06:46:52 GMT

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.

RE: GOTO and T-SQL

batesview — Thu, 22 Dec 2011 05:51:32 GMT

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.

RE: GOTO and T-SQL

Resender — Thu, 22 Dec 2011 04:22:31 GMT

In an age when animal could still speak...Ok, not that long ago, about 12 years ago I got my first programming lessons I used the GOTO instead of using OO functions.Over the years as my knowledge of programming grew and my enthusiasm to program grew less, I started to replace the GOTO less.I didn't even know that it was in the SQL options.In programming I can think of one use to still use it, to quickly skip code your debugging but the project can no longer wait to go live.

RE: GOTO and T-SQL

Andrew Watson-478275 — Thu, 22 Dec 2011 03:25:01 GMT

I found myself using one just the other day, and although it was by far the neatest and most readable option for the logic I was using, I still felt the need to add an apology in the comments!Like everything (even, dare I say it, cursors), it's got its place, but anything more than occasional use should be a pointer for closer scrutiny.

RE: GOTO and T-SQL

call.copse — Thu, 22 Dec 2011 03:21:58 GMT

I think GOTO is great and like to employ it at every opportunity. How else do you sensibly structure conditional exit points?Just kidding. I don't think I've ever felt the need in my SQL or code of any sort - save perhaps some error handling in Access if I can remember that far back? Anyway I would not be scared if I did need to use it it's fair to say.

RE: GOTO and T-SQL

David.Poole — Thu, 22 Dec 2011 03:11:16 GMT

I very rarely use GOTO, in fact I cannot remember when I last used it.If I have a large stored proc with a complex transaction then on failure of any part of the transaction I might use GOTO to jump to the ROLLBACK TRANSACTION code.To be honest, TRY...CATCH handles most scenarios for me.GOTO is useful when some processing logic results in a value that indicates that the rest of the transactional process should not proceed.The alternative is to set a BIT variable to TRUE and after each step in the transaction set it to FALSE if the success criteria is not met.Wrap each step in the transaction up in IF @SuccessFLAG=1 BEGIN ... END

RE: GOTO and T-SQL

Samuel Vella — Thu, 22 Dec 2011 02:56:41 GMT

It can work the other way too, where something is promoted early in an article and then disparaged straight after. Of course, once a reader has read the for arguements, they feel that they understand the subject and decide not to read any more.The waterfall accident: [url=http://pascal.gugenberger.net/thoughts/waterfall-accident.html]http://pascal.gugenberger.net/thoughts/waterfall-accident.html[/url]

RE: GOTO and T-SQL

ChiragNS — Thu, 22 Dec 2011 02:36:37 GMT

I like this sentence in the article.[quote]It is a shame that useful, but subtle, guidance can morph so rapidly into a ‘policy’ that becomes ‘mandatory’. [/quote]

RE: GOTO and T-SQL

IceDread — Thu, 22 Dec 2011 02:33:49 GMT

I have never felt the need to write a goto statement.

RE: GOTO and T-SQL

Riken Patel — Thu, 22 Dec 2011 02:20:54 GMT

Well said. When I read the first part of your paragraph I was shocked to see that I am being told not to use GOTO.I had a similar reaction when reading an article about not using DBCC SHRINKFILE, at the start it was NO NO NO. But reading the whole article tells you that is not the case. I'm sure many people must read the first bit and leave it at that.

RE: GOTO and T-SQL

Jeff Moden — Wed, 21 Dec 2011 23:31:37 GMT

Well said, Phil. Perhaps this very well written editorial could be followed by a deeper article on why people shouldn't jump the gun because they read a title or an article by a famous author. Like Sergiy taught me, "A Developer must NOT GUESS! A Developer must know." The only way to know is to do some additional research and conduct your own tests with an open mind.I've run into similar problems. For example, I've been told by a couple of friend DBAs in the past that we don't have to concern ourselves with properly sizing a database, that SQL Server was built to automatically handle all of that right from the startup of a new server. The information came from a sentence in the front of a book written by a very well known and respected author. People just don't understand that even "experts" like well known authors can make a "mistrake". ;-)

GOTO and T-SQL

Phil Factor — Wed, 21 Dec 2011 22:30:42 GMT

Comments posted to this topic are about the item [B]GOTO and T-SQL[/B]