April 2, 2009 at 8:25 am
Here's a general best practices question about developing stored procedures in t-sql. Is it better to wrap the contents of the sproc in a begin /end block or not? and why?
Example: If I create a stored proc
Create procedure MyStoredProc
as
BEGIN
... stored proc code
END
Versus
Create procedure MyStoredProc
as
... stored proc code
Which is a better programming practice and why?
Thanks!
April 2, 2009 at 8:47 am
I haven't found that it actually affects anything at all. It's probably better to include it, just on the general principle that explicit coding is better than implicit, even if just so other people will understand things. Have to admit that I don't do it, at least not as a general practice.
I just ran a speed test on these two procs:
create proc Test1
as
begin
set nocount on;
declare @Var int;
select @Var = 1;
end;
go
create proc Test2
as
set nocount on;
declare @Var int;
select @Var = 1;
Ran each 1-million times, execution time was identical. Ran each 1-million times again, execution time was 50 milliseconds better, total, for the one without the Begin End. Ran each 1-million times a third time, total execution time was identical. Precision was milliseconds on all tests.
- 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
April 2, 2009 at 8:52 am
I generally do include it, not sure what the difference is.
I suspect it may come into play if there are errors in the block of code.
I think it's requried in oracle, not sure been quire some time since I've written pl/sql code.
April 2, 2009 at 8:54 am
Personally, I use the BEGIN/END approach as it binds the logical block of statements to the context it has been created in. And thereby making it clear to understand the flow of the action.
In fact, I follow the same approach to the control-of-flow statements (i.e. IF, WHILE, TRY...CATCH etc.) as well.
--Ramesh
April 2, 2009 at 10:12 am
I use
BEGIN
...
END
whenever I can, except 1 line IF statement block that can omit BEGIN...END
Easier for collapse/expand in SSMS too
April 2, 2009 at 3:31 pm
Don't shoot me, but I stopped using BEGIN-END in procedures since I noticed it has no effect. You can write as much BEGIN-END BEGIN-END blocks into a procedure as you want. I defined for me the procedure body starts with AS and ends with either GO or eof.
Greets
Flo
April 2, 2009 at 5:42 pm
I have found one case where it matters, but it is obscure. That is, when executing generated script that are rare case when SQLCMD or a query window executing said script will get confused about where the end of a stored procedure should be which can lead to a huge pile-up of subsequent errors. I have found that using BEGIN..END fixes this.
Sinc it doesn't hurt anything and there is at least one case where it helps, I would have to say that it is a Best Practice. Even though I typically do not do it myself. :Whistling:
[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]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply