June 12, 2014 at 4:56 am
We have a discussion going on internally. Some devs prefer doing this:
CREATE PROC x as
BEGIN
--do stuff
END
while others prefer
CREATE PROC x as
--do stuff
As far as I know, they are identical in effect.
I prefer version 2 because
a) It does not contain superfluous statements and avoids making (some) developers waste mental energy thinking "I wonder why that is there?"
b) There is less likelihood of long rows requiring scrolling to the right of the screen.
Those who prefer version 1 seem to do so because the explicit proc termination makes them feel more comfortable.
Redgate's new SQL Code Guard tool even has a rule - ST003:
"Procedure body not enclosed with BEGIN...END
It is recommended to enclose a routine body in BEGIN...END block."
I'd like to know who made that recommendation and why.
I'd like to know what others think.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 12, 2014 at 5:03 am
I agree with you there Phil. I don't really see the point of enclosing the proc in the the BEGIN END block when it works just as well without. There's definitely a risk of somebody new coming to the code and wasting time thinking 'why's that like that, what does it do differently', particularly if they're reasonably new to the game.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 12, 2014 at 5:12 am
It might be a recommendation for consistency, since functions do require the BEGIN ... END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2014 at 5:31 am
I use the BEGIN...END for procedures because that's how I learned many moons ago. Gail's right - it is consistent with functions. In fact, when writing an iTVF, I have to consciously not type it. Typically, my release scripts contain multiple tables, procedures, functions, etc. and it also gives me an outer level to my indenting, which helps me keep track of where I am. I know indenting may seem silly, but perhaps it's just what I'm used to doing.
Is it necessary for procedures? No.
Is it a best practice? Not sure, but many things get passed around as "best practices" nowadays, even if they're wrong.
Is it a performance gain? No, not as far as I know.
Do I still use it? Yes.
But, thanks for making me consider I simply take for granted.
June 12, 2014 at 9:29 am
Should really be a begin try with a subsequent error handling begin catch
June 12, 2014 at 9:51 am
tim.berry 88829 (6/12/2014)
Should really be a begin try with a subsequent error handling begin catch
I would bot be a big fan of that. There are times that a procedure should fail and we want to inform the calling application and not log anything.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2014 at 9:56 am
tim.berry 88829 (6/12/2014)
Should really be a begin try with a subsequent error handling begin catch
I'm not sure that I would like to have a single try block for complex procedures.
June 12, 2014 at 10:09 am
Horses for error handling courses i.e. whether there is a try catch or not is dependant upon your requirements. The default to fail over to the operating system but there is sometimes a need for controlled error handling within the procedure. There doesn't have to be one block such blocks are quite often nested and prioritised dependant again on the requirements.
June 12, 2014 at 5:46 pm
tim.berry 88829 (6/12/2014)
Should really be a begin try with a subsequent error handling begin catch
tim.berry 88829 (6/12/2014)
Horses for error handling courses i.e. whether there is a try catch or not is dependant upon your requirements. The default to fail over to the operating system but there is sometimes a need for controlled error handling within the procedure. There doesn't have to be one block such blocks are quite often nested and prioritised dependant again on the requirements.
I agree but there are a lot of people that include Try/Catch as a matter of rote (and it sounded like that's where you were going with this) and then they forget to rethrow things like what the actual error and error line was. If that's all anyone is going to do, then forget the Try/Catch and let the system do what it already does quite well. As you say, if there's something specific someone wants to happen on an error, then a Try/Catch is definitely in order.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2014 at 5:50 pm
I prefer not to use BEGIN/END on my procedure block, but I do trail the script for it with a GO so I can be sure of the termination point (and my GRANT/etc block at the tail is a little more separated).
As to TRY/CATCH... I only use it if I have something to do with the CATCH besides kick the error out, similar to Jeff above... the SQL Server already reports its error quite well. Unless I need to rollback a transaction or something similar on its way out, or perform a completely different set of actions (IE: Add in the missing value into the FK table), why bother? It's just clutter.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 12, 2014 at 7:12 pm
Ed Wagner (6/12/2014)
I use the BEGIN...END for procedures because that's how I learned many moons ago. Gail's right - it is consistent with functions. In fact, when writing an iTVF, I have to consciously not type it. Typically, my release scripts contain multiple tables, procedures, functions, etc. and it also gives me an outer level to my indenting, which helps me keep track of where I am. I know indenting may seem silly, but perhaps it's just what I'm used to doing.Is it necessary for procedures? No.
Is it a best practice? Not sure, but many things get passed around as "best practices" nowadays, even if they're wrong.
Is it a performance gain? No, not as far as I know.
Do I still use it? Yes.
But, thanks for making me consider I simply take for granted.
Because of today's systems, I can no longer prove it but, on many of the systems I worked on almost 2 decades ago, BEGIN/END caused my high hit ratio procs to run slower than without. With that in mind, I never developed the habit.
My problem of late with it is indentation. I've drilled the idea of proper indentation into the heads of the Developers that I work with along with several other "company standards". One of those standards is also a max line length of 119 characters (stop when the cursor is on 120) and so wasting 4 or 8 characters of every line except the BEGIN/END seems like both a chore and a waste.
As a bit of a sidebar, the 119 character limit is for 2 reasons. The first is that with the Object Explorer open, that's about the most you can see on "average" monitors without having to scroll horizontally. It's also the max number of characters that will fit on a piece of paper in the landscape mode with a half inch left and right margins at 10 pt Courier New (or other mono-spaced font) without wrapping automatically.
Even if we didn't indent for it (Begin/End), it also really ticks me off when I try to run a part of the proc and it says "Error" at the first line because I accidentally included the last line of the proc, which would be the "End;".
As for being more like functions and, therefor, supposedly more standard, I usually try to avoid any type of function that requires the use of BEGIN/END (scalar and mTVFs require it) for reasons of performance. I generally use only iTVFs and iSFs (an iTVF that returns a single value), neither of which will take BEGIN/END.
Shifting back to some of the previous posts on this thread, I always use alias = expression in SELECTs and INSERTs for two reasons. Much like people use BEGIN/END in procs because some functions require it, I've found it real easy to convert and test an UPDATE if the SELECT is written that way. I also write some fairly long formulas a lot and we have column names that are anywhere from 2 characters to a sometimes very high number of characters (sometimes necessary, sometimes not, sometimes bloody ridiculous). As a result, it's easy to "lose" an AS alias in the clutter. With the alias = expression method, I know that all aliases are going to start in the same vertical column and that makes them really easy to find for troubleshooting purposes. People who end up reading my code seem to really like it even if they don't follow the paradigm in their own code.
As Bill Cosby said in his famous "Fat Albert/Buck-Buck" story, "I told you that story so I could tell you this one"... 😛
One of my biggest pet peeves is inconsistency in code style within a proc or other type of SQL module. Oh, sure... I insist that a Developer making a mod to a piece of undocumented code add the "company standard" header and add comments for each Insert, Select, Update, and Delete (a whole 'nuther story) that they touch (more if they have an extra minute or two), but if the majority of the code is written with a certain type of standard alignment and uses AS alias instead of alias = expression, then I tell them to continue the trend. Of course, some of our legacy code was written and modified by many people who either had a standard that sucks or no standard at all and that's when I encourage the Developers to reformat the areas they change and, if they have time, at least reformat some of the ridiculously long lines that are well in excess of the 119 character standard. Of course, any new code modules (including total rewrites) must meet all of the "company standards".
I had folks do the same things in a company that I previously worked at. The existing code was so poorly written it sometimes took a couple of days just to figure out where a change needed to be made in the longer procs (many were 500 to 4000 lines long... 'nuther story there that can be summarized as extremely poor performing, highly resource intensive, "crap code" on steroids). The rule was, "If you have to touch it, you have to fix it" for the shorter code or the sections you worked on for the longer code. It normally only took a Developer 5 or 10 minutes extra each time but after a while, there was less and less to fix, reformat, or document. After just two years, the average rework time dropped from 1-2 work days (for the easy stuff) down to just several minutes. Better than that, the number of reworked-code failures (couldn't tell that a fix in one part of the code usually broke another) dropped from an average of 3 cycles back and forth with QA down to no returns from QA.
As for it taking longer to follow standards, it does take a second or two longer per line when you first start. Once you get used to it, it takes no extra time and, compared to the amount of rework that needed to be done and the time it took to research code to figure out how to modify it, it was definitely worth it by a couple of orders of magnitude. Surprisingly, the overall performance of the code benefited, as well. During the reformatting (as we've done here on SSC more times than not), the Developer would spot an "anomoly" and fix it. I lost count at the old company of how many 8-12 hour "nightly jobs" that would suddenly start running in 10 to 15 minutes. It was amazing to behold the look on the faces of Developers when I told them how well their rework or their new code actually performed in production. I made sure their respective bosses knew, too.
That brings me to my largest pet peeves of all... no "company standards", no peer reviews to enforce the standards, no purposeful in-house training, no thoughtful mentoring by the DBAs, Developers being allowed to promote their own code to production, and no management buy-in to control any of that.
For all you Managers out there, remember just one thing... "If you want it real bad, that's the way you'll get it". :sick: Help your Team make YOU look like a star. Insist on company standards, peer review, in-house training, mentoring by seniors, etc, etc, and provide management buy in for it all. And give them the power to say "Guh!!! No... that ain't right"! It'll take things a little longer to get done until the Team settles into the new groove, but then watch them soar and drag you up with them.
Sorry about the long winded post but I've got more 40 years of pet peeves and thought I'd share some of them with you. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2014 at 7:13 am
Jeff Moden (6/12/2014)
Ed Wagner (6/12/2014)
I use the BEGIN...END for procedures because that's how I learned many moons ago. Gail's right - it is consistent with functions. In fact, when writing an iTVF, I have to consciously not type it. Typically, my release scripts contain multiple tables, procedures, functions, etc. and it also gives me an outer level to my indenting, which helps me keep track of where I am. I know indenting may seem silly, but perhaps it's just what I'm used to doing.Is it necessary for procedures? No.
Is it a best practice? Not sure, but many things get passed around as "best practices" nowadays, even if they're wrong.
Is it a performance gain? No, not as far as I know.
Do I still use it? Yes.
But, thanks for making me consider I simply take for granted.
Because of today's systems, I can no longer prove it but, on many of the systems I worked on almost 2 decades ago, BEGIN/END caused my high hit ratio procs to run slower than without. With that in mind, I never developed the habit.
My problem of late with it is indentation. I've drilled the idea of proper indentation into the heads of the Developers that I work with along with several other "company standards". One of those standards is also a max line length of 119 characters (stop when the cursor is on 120) and so wasting 4 or 8 characters of every line except the BEGIN/END seems like both a chore and a waste.
As a bit of a sidebar, the 119 character limit is for 2 reasons. The first is that with the Object Explorer open, that's about the most you can see on "average" monitors without having to scroll horizontally. It's also the max number of characters that will fit on a piece of paper in the landscape mode with a half inch left and right margins at 10 pt Courier New (or other mono-spaced font) without wrapping automatically.
Even if we didn't indent for it (Begin/End), it also really ticks me off when I try to run a part of the proc and it says "Error" at the first line because I accidentally included the last line of the proc, which would be the "End;".
As for being more like functions and, therefor, supposedly more standard, I usually try to avoid any type of function that requires the use of BEGIN/END (scalar and mTVFs require it) for reasons of performance. I generally use only iTVFs and iSFs (an iTVF that returns a single value), neither of which will take BEGIN/END.
Shifting back to some of the previous posts on this thread, I always use alias = expression in SELECTs and INSERTs for two reasons. Much like people use BEGIN/END in procs because some functions require it, I've found it real easy to convert and test an UPDATE if the SELECT is written that way. I also write some fairly long formulas a lot and we have column names that are anywhere from 2 characters to a sometimes very high number of characters (sometimes necessary, sometimes not, sometimes bloody ridiculous). As a result, it's easy to "lose" an AS alias in the clutter. With the alias = expression method, I know that all aliases are going to start in the same vertical column and that makes them really easy to find for troubleshooting purposes. People who end up reading my code seem to really like it even if they don't follow the paradigm in their own code.
As Bill Cosby said in his famous "Fat Albert/Buck-Buck" story, "I told you that story so I could tell you this one"... 😛
One of my biggest pet peeves is inconsistency in code style within a proc or other type of SQL module. Oh, sure... I insist that a Developer making a mod to a piece of undocumented code add the "company standard" header and add comments for each Insert, Select, Update, and Delete (a whole 'nuther story) that they touch (more if they have an extra minute or two), but if the majority of the code is written with a certain type of standard alignment and uses AS alias instead of alias = expression, then I tell them to continue the trend. Of course, some of our legacy code was written and modified by many people who either had a standard that sucks or no standard at all and that's when I encourage the Developers to reformat the areas they change and, if they have time, at least reformat some of the ridiculously long lines that are well in excess of the 119 character standard. Of course, any new code modules (including total rewrites) must meet all of the "company standards".
I had folks do the same things in a company that I previously worked at. The existing code was so poorly written it sometimes took a couple of days just to figure out where a change needed to be made in the longer procs (many were 500 to 4000 lines long... 'nuther story there that can be summarized as extremely poor performing, highly resource intensive, "crap code" on steroids). The rule was, "If you have to touch it, you have to fix it" for the shorter code or the sections you worked on for the longer code. It normally only took a Developer 5 or 10 minutes extra each time but after a while, there was less and less to fix, reformat, or document. After just two years, the average rework time dropped from 1-2 work days (for the easy stuff) down to just several minutes. Better than that, the number of reworked-code failures (couldn't tell that a fix in one part of the code usually broke another) dropped from an average of 3 cycles back and forth with QA down to no returns from QA.
As for it taking longer to follow standards, it does take a second or two longer per line when you first start. Once you get used to it, it takes no extra time and, compared to the amount of rework that needed to be done and the time it took to research code to figure out how to modify it, it was definitely worth it by a couple of orders of magnitude. Surprisingly, the overall performance of the code benefited, as well. During the reformatting (as we've done here on SSC more times than not), the Developer would spot an "anomoly" and fix it. I lost count at the old company of how many 8-12 hour "nightly jobs" that would suddenly start running in 10 to 15 minutes. It was amazing to behold the look on the faces of Developers when I told them how well their rework or their new code actually performed in production. I made sure their respective bosses knew, too.
That brings me to my largest pet peeves of all... no "company standards", no peer reviews to enforce the standards, no purposeful in-house training, no thoughtful mentoring by the DBAs, Developers being allowed to promote their own code to production, and no management buy-in to control any of that.
For all you Managers out there, remember just one thing... "If you want it real bad, that's the way you'll get it". :sick: Help your Team make YOU look like a star. Insist on company standards, peer review, in-house training, mentoring by seniors, etc, etc, and provide management buy in for it all. And give them the power to say "Guh!!! No... that ain't right"! It'll take things a little longer to get done until the Team settles into the new groove, but then watch them soar and drag you up with them.
Sorry about the long winded post but I've got more 40 years of pet peeves and thought I'd share some of them with you. 😛
I seems like the longer we've been writing code, the move pet peeves we have. I have a few, and this is where my wife would point out that this is just me being obstinate. 😛
I am completely with you on formatting. Some people say that it's silly, it doesn't impact performance and a host of other reasons for not doing it. I don't view it as performance of the code, but rather the performance of the person writing it. Well-formatted code (in whatever convention you use) impacts the way you read it. Once you start reading it (as opposed to interpreting it), you can focus on what's written instead of how it's written. It also makes "anomalies" more visible.
I love your statement "If you want it real bad, that's the way you'll get it". I think I may have to use that one. 😉
June 13, 2014 at 7:24 am
May I thank you all for the comments and insight, especially Jeff for the detailed and interesting response.
I shall continue omitting the superfluous BEGIN/END, though I won't complain too much if others choose to keep it. And I won't expect them to complain too much if I helpfully remove it while editing other parts of their code :w00t:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply