August 19, 2008 at 1:13 pm
Hi all,
As per MSDN the syntax of stored proc is...
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
] [ ,...n ]
[ WITH [ ,...n ] ]
[ FOR REPLICATION ]
AS { }
[;]
::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]
::=
{ [ BEGIN ] statements [ END ] }
::=
EXTERNAL NAME assembly_name.class_name.method_name
As we know already the outer [BEGIN] [END] keywords are optional. So here is my question... Are you guys aware of any reason why we should use or not use [BEGIN] [END]?
I am wondering simply because certain things are optional (i.e. like the semi-column ";" at the end of statements) but it's preferrable to use them or not use them... Often for backward or future compatibility purposes... Any idea?
So...
CREATE PROCEDURE schema.spname
AS
{stuff....}
OR
CREATE PROCEDURE schema.spname
AS
BEGIN
{stuff....}
END
August 19, 2008 at 1:18 pm
I don't normally use BEGIN-END blocks around my stored procedure code. I do always use BEGIN-END blocks with control of flow statements (IF, WHILE) though.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 19, 2008 at 2:08 pm
I posed a similar question a while back....http://www.sqlservercentral.com/Forums/FindPost459604.aspx
Kev
August 19, 2008 at 8:16 pm
I don't bother with them. They currently have no function, and they don't make the proc more clear to me.
One of the developers at the place I work uses them every time. He's heavily procedural (lots of cursors, table variables to do step-by-step Where's, etc.), so it's probably just part of that mindset.
- 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
August 19, 2008 at 9:36 pm
I tend not to use them for aesthetic reasons as much as anything. I do put the semicolon after every statement now though .... it is mandatory with some statements (or combinations of statements) and my guess is it will become mandatory for all at some point in the future. It also makes one less change when converting SQL code to Oracle.
August 20, 2008 at 12:03 am
I do put the semicolon after every statement now though .... it is mandatory with some statements (or combinations of statements)
Hi Matt
Can you please give me example of situations where it was mandatory for you to use semicolon at end of statement.
It also makes one less change when converting SQL code to Oracle.
Agreed on this.
August 20, 2008 at 2:22 am
hi,
when your following tsql is a CTE ("WITH") try it 🙂 it'll ask u to add one ";"
as for my comments on Begin/End use it wisely previously i edited someonelse SP, it builds fine. But when my application calls it, it ended up with a timeout, and my application stalls there.
I on profiler and get e sql execute cmd and run that exact query under MSMS miraculously it completes in one second.
So be careful, make sures the no. of begin matches with the no. of end
Cheers,
Jon
August 20, 2008 at 2:27 am
Hi
I agree its part of a mindset but using them makes it clear where procedure starts and ends... Especially when you have lot of Control flow statements.
"Keep Trying"
August 20, 2008 at 6:10 am
As has already been stated, there are places where you have to use it, but, no, I don't put it around the code in my stored procedures since it does nothing for them (or to them). I'm lazy and anything I don't have to use, I won't. However, I too try to use semi-colons at the end of each statement, which, since you only need them before a CTE (at this time), it kind of goes against my laziness principal, but there you go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2008 at 8:14 am
From memory I think there is also a requirement to use the semicolon between some of the service broker statements and in some of the new 2008 functionality, I'd have to dig through for specifics though.
August 21, 2008 at 10:47 am
Chirag (8/20/2008)
HiI agree its part of a mindset but using them makes it clear where procedure starts and ends... Especially when you have lot of Control flow statements.
I always thought of the procedure's beginning as the keyword "as" after the create statement and any parameters. Following "as" with "begin" always seemed redundant to me.
And the end of the proc is the last line of code. Adding "end" there doesn't make it any easier for me to find. If it's part of a multi-statement script, I have to look for "go" anyway, since "end" can just be the end of an "if" block or "where" loop.
If it helps you read the proc, include it. It doesn't help me. Doesn't hinder me, either, but doesn't help. So I don't bother with it.
It's a pretty minor point, either way. Go with whatever you're comfortable with.
- 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
August 21, 2008 at 11:41 am
matt stockham (8/20/2008)
From memory I think there is also a requirement to use the semicolon between some of the service broker statements and in some of the new 2008 functionality, I'd have to dig through for specifics though.
Yes, that is correct, Matt. SEND and RECEIVE both require a semicolon before them unless they are the first statement in a block. Just like WITH...
[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 21, 2008 at 12:00 pm
It is a mind set, but not necessarily a mind set of a procedural mind. I use the begin end around my procedures. When I write them I start with a template that looks something like this:
create procedure [schema].[procname]
param1 datatype,
param2 datatye
as
begin
end;
I guess it comes from trying to write structured code. I use lots of white space as well.
😎
August 21, 2008 at 12:10 pm
GSquared (8/21/2008)
I always thought of the procedure's beginning as the keyword "as" after the create statement and any parameters. Following "as" with "begin" always seemed redundant to me.
This is how I have always felt and I add that the procedure ends when I put in the RETURN as I always have a RETURN at the end of my procedures.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply