August 20, 2008 at 11:33 am
Thank you, J - that was very helpful and I really appreciate your time.
Kenena
August 20, 2008 at 11:35 am
I forgot to add that this is a coding style, NOT a "rule".
August 20, 2008 at 12:08 pm
kevriley (2/25/2008)
Is there any advantage/disadvantage to including the BEGIN and END block in a simple stored proc, i.e.CREATE PROC MyProc
AS
BEGIN
select col1, col2 from Mytable
END
no control-flow statements, no transaction.
Disadvantge: extra ugly stuff; does not contribute information so basically it's visual chaff/clutter.
Advantage: consistency, if you always do it that way, stay consistent.
-----------
A little known fact is that 99% of the time, you do not need it at all for the procedure definition itself, even in very complex procs. (Of course you still need them for interior blocks.)
However, every once in a while I come across one that MUST have them in order to be able to script and re-execute it correctly in the middle of a bunch of other procedure scripts.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 20, 2008 at 2:48 pm
Michael Meierruth (8/20/2008)
Jeff Moden (2/25/2008)
...Might be an ANSI standard but I always follow MIL-TP-41H, instead... 😉I have forgiven myself for not knowing the meaning of that abbreviation. But the same has not happened for "Make It Like The Print For Once Heh". And here I thought my English was top notch. So what is the meaning of that?
So no attempts at the meaning of that phrase?
Believe me, I can sleep soundly thinking about BEGIN/END and GO.
But this phrase is keeping me awake.
And I'm scared to bits if it should turn out to be something very simple - making me look like a total fool.
August 20, 2008 at 3:34 pm
As for preferences, I always use the Begin/End in IF/ELSE constructs but also for stored procs and, yes, I indent the entire block of code. This is personal preference but let me show you where it would have helped, probably, just last week.
Someone (name omitted to protect the guilty) wrote a simple little sproc. The script looked like this:
create procedure procname
as
statement 1
statement 2
...
statement n
grant execute to procname to role1
grant execute to procname to role2
go
He tested it (it passed), QA tested it (it passed), and it went to production (it bombed). It was sent to me for troubleshooting. Before I even looked at the code, I first added 'begin' after 'as', highlighted the first statement after it and scrolled to the bottom to shift-select and indent. I had a little chuckle when I saw the end of the code and knew immediately that the logic of the sproc was probably sound, the problem was that the users were getting "No execute permission" messages.
Having something that stands up, waves its arms and shouts "this is the last line of the procedure" can be of immense help. It is also why I end my statements with ';'. It's not needed (usually) by the compiler. It just helps me identify the end of the executable lines as my eyes wander over the code.
Tricks like this have a very important function: it can help make wrong code look wrong (http://www.joelonsoftware.com/articles/Wrong.html).
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
August 20, 2008 at 5:04 pm
Tomm, I believe this version of the code
create procedure procname
as
BEGIN
statement 1
statement 2
...
statement n
END
grant execute to procname to role1
grant execute to procname to role2
go
would change nothing and users would still receive the same error message in Production because of the same reason.
So, I don't see a point really.
_____________
Code for TallyGenerator
August 20, 2008 at 5:18 pm
But this would:
create procedure procname
as BEGIN
statement 1
statement 2
...
statement n
END
grant execute to procname to role1
grant execute to procname to role2
go
The eye is more likely to notice all that unindented stuff at the end where there is not supposed to be any. It's not a guarantee, of course. All you're doing is increasing the probability that you'll notice something standing out.
And you still have to know to put a "GO" after the "END". 😉
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
August 20, 2008 at 7:03 pm
Michael Meierruth (8/20/2008)
Michael Meierruth (8/20/2008)
Jeff Moden (2/25/2008)
...Might be an ANSI standard but I always follow MIL-TP-41H, instead... 😉I have forgiven myself for not knowing the meaning of that abbreviation. But the same has not happened for "Make It Like The Print For Once Heh". And here I thought my English was top notch. So what is the meaning of that?
So no attempts at the meaning of that phrase?
Believe me, I can sleep soundly thinking about BEGIN/END and GO.
But this phrase is keeping me awake.
And I'm scared to bits if it should turn out to be something very simple - making me look like a total fool.
Sorry for the delay...
The phrase "Make It Like The Print For Once" was a saying we had in the Navy long before ISO 2000 and 2003 were even a gleam in someone's eye. It's the short and nasty way of trying to describe the spirit of some of those ISO standards. Other's say "Say what you do, do what you say" about the standards.
The "Heh" at the end is more like an OK? If someone asked me to turn the phrase into a more meaningful and substantially longer sentence, it would be something like "Look! We've tried the seat-of-our-pants design method several times, already, and it failed every time. Let's "make it like the print for once", OK???" 🙂 The super short version would simple be "RTFS". :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2008 at 8:22 pm
Tomm Carr (8/20/2008)
But this would:
create procedure procnameas BEGIN
statement 1
statement 2
...
statement n
END
grant execute to procname to role1
grant execute to procname to role2
go
The eye is more likely to notice all that unindented stuff at the end where there is not supposed to be any. It's not a guarantee, of course. All you're doing is increasing the probability that you'll notice something standing out.
And you still have to know to put a "GO" after the "END". 😉
I'm thinking that knowing where to put the GO is only part of the problem in that code... it's still gonna crash the way it is... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2008 at 8:23 pm
Sergiy (8/20/2008)
Tomm, I believe this version of the code
create procedure procname
as
BEGIN
statement 1
statement 2
...
statement n
END
grant execute to procname to role1
grant execute to procname to role2
go
would change nothing and users would still receive the same error message in Production because of the same reason.
So, I don't see a point really.
Spot on... times 2 plus 1. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply