December 21, 2011 at 10:30 pm
Comments posted to this topic are about the item GOTO and T-SQL
Best wishes,
Phil Factor
December 21, 2011 at 11:31 pm
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". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2011 at 2:20 am
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.
December 22, 2011 at 2:33 am
I have never felt the need to write a goto statement.
December 22, 2011 at 2:36 am
I like this sentence in the article.
It is a shame that useful, but subtle, guidance can morph so rapidly into a ‘policy’ that becomes ‘mandatory’.
"Keep Trying"
December 22, 2011 at 2:56 am
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: http://pascal.gugenberger.net/thoughts/waterfall-accident.html
December 22, 2011 at 3:11 am
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
December 22, 2011 at 3:21 am
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.
December 22, 2011 at 3:25 am
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.
December 22, 2011 at 4:22 am
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.
December 22, 2011 at 5:51 am
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.
December 22, 2011 at 6:46 am
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.
December 22, 2011 at 7:07 am
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.
December 22, 2011 at 7:11 am
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
December 22, 2011 at 7:48 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply