February 25, 2008 at 4:12 am
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.
Any thoughts?
Kev
February 25, 2008 at 6:30 am
I know of no advantage... I did a test once on a 10 million row table where it seemed to run a couple ms faster without BEGIN/END... but nothing real conclusive.
So far as opinion goes, I don't use it. Really does nothing for the code that I can see. Might be an ANSI standard but I always follow MIL-TP-41H, instead... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2008 at 7:32 am
Jeff Moden (2/25/2008)
I know of no advantage... I did a test once on a 10 million row table where it seemed to run a couple ms faster without BEGIN/END... but nothing real conclusive.So far as opinion goes, I don't use it. Really does nothing for the code that I can see. Might be an ANSI standard but I always follow MIL-TP-41H, instead... 😉
Now - don't go playing dirty with acronyms he isn't going to recognize....:D That's one of Jeff's pet sayings, standing for "Make It Like The Print For (4) Once (1), heh?"
It's a matter of programming style. I personally think it makes sense to have a BEGIN and END around every logical chunk of code (even small ones). Like has been said before - it neither adds nor detracts from anything the server would have to do.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 25, 2008 at 7:54 am
Well I'll be darned... you remembered that? 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2008 at 7:55 am
Google is a powerful powerful thing......:cool: ( I had forgotten what the P was for...:D)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 25, 2008 at 8:15 am
I was wondering.......
February 25, 2008 at 8:15 am
I never use them.
However I do end stored procs with a RETURN just for convention and to make it clear to myself that I'm done coding in this batch.
February 26, 2008 at 8:46 am
I've seen code like this (.. is an indentation, this forum strips leading spaces?!?):
IF somecondition
..dosomething
where the program will come along later and add another command after the IF thinking that it will conditionally execute because its at the same indent level (ya, don't go there...)
IF somecondition
..dosomething
..dosomethingelse
If you always include the BEGIN/END syntax this problem would be less likely to occur.
IF somecondition BEGIN
..dosomething
..dosomethingelse
END
February 26, 2008 at 9:03 am
I always ident by 4 spaces the code between a ...BEGIN and the END statement.
For instance:
[font="Courier New"]IF 'COSMO' = 'KRAMER' BEGIN
....PRINT 'This will never show up'
END
ELSE B$EGIN
....PRINT 'No other outcome possible'
END[/font]
-- or --
[font="Courier New"]DECLARE @li_Counter int
SET @li_Counter = 1
WHILE @li_Counter <= 100 BEGIN
....SELECT @li_Counter
....SET @li_Counter = @li_Counter + 1
END[/font]
[font="Arial"]So when the first line of a stored procedure (after the "AS") is a BEGIN statement, then the entire stored procedure code is indented by 4 spaces, not contributing to code readability.
And, YES, whether to place the BEGIN keyword at the end of the IF / WHILE line or placing it indented as the next line is the object of pro and con arguments boiling down to, essentially PERSONAL preference. I just happen to prefer the first form over the latter. Do what you want (unless a coding standard is enforced at the workplace).
As for the RETURN statement, I strongly advocate:
1. Using it
2. Using it to return a value.
Such as[/font]
[font="Courier New"]
CREATE PROCEDURE myOwn
AS
SET NOCOUNT ON
DECLARE @li_RetCode int
SET @li_RetCode = -666
INSERT INTO myTable2
.... SELECT * FROM myTable1[/font] [font="Arial"](you get the picture)[/font]
[font="Courier New"]IF @@ERROR <> 0 SET @li_RetCode = -1 ELSE SET @li_RetCode = 0
IF @li_RetCode = 0 BEGIN
UPDATE myTable2 SET[/font] [font="Arial"]... (you get the picture)/font]
[font="Courier New"]....IF @@ERROR <> 0 SET @li_RetCode = -2
END
RETURN @li_RetCode[/font][/size]
GO
[font="Arial"]The value returned by the stored proc can at least indicate which step failed, Aiding in debugging.
In cases where the code is nested in side a transaction, then I like to use the (Quick, hide the children) GOTO statement and handle the transaction (commit or rollback) at the end, just before exiting the stored proc.
This is why I prefer testing the [/font][font="Courier New"]@li_RetCode [/font][font="Arial"]value before each step, instead of simply writing[/font]
[font="Courier New"]IF @@ERROR <> 0 SET @li_RetCode = -2
IF @li_RetCode <> 0 RETURN @li_RetCode
[/font]
[font="Arial"]-- or --[/font]
[font="Courier New"]IF @li_RetCode <> 0 RETURN -2[/font]
August 19, 2008 at 2:20 pm
I'll add my 2 cents here as well, since I found this thread through another, very similar thread.
I don't use the BEGIN-END block for stored procedures. I DO use BEGIN-END with all control of flow statements (IF, WHILE) for the reasons mentioned by others.
I do usually use a RETURN and if an error occurs I try to remember to return a meaningful, at least to me, value based on where the error occurred.
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 20, 2008 at 7:14 am
About return values:
I usually comment my code by breaking it down in sections (and subsections), which I number sequentially. And form each section number, I will use a return value based on this numbering scheme.
For instance:
[font="Courier New"]DECLARE @li_RetCode int
SET @li_RetCode = -1
-- ---------------------------
-- 1 INITIALIZE SOMETHING
-- ---------------------------
UPDATE myTable1 ...
IF @@EEROR <> 0 SET @li_RetCode = -100 ELSE SET @li_RetCode = 0
IF @li_RetCode <> 0 RETURN @li_RetCode
UPDATE myTable2 ...
IF @@EEROR <> 0 SET @li_RetCode = -101
IF @li_RetCode <> 0 RETURN @li_RetCode [/font]
You get the picture. This gives me, as a starting point, the exact line where the problem occurred.
Also, the application calling the stored procedure always checks to see if the return value is zero to report failure.
Yes I know, there have been occasions where I add to insert a new section in between two sequentially numbered sections, forcing me to renumber each section and return values from the point of insertion down to the end of the code. I can live with that.
August 20, 2008 at 10:56 am
Hello,
I am creating a stored procedure that will run 6 other stored procedures. Why do you say not to use the BEGIN/END. Also where does GO play a part for this? Each of the procedures has updates for different tables, and I put a BEGIN and END followed by a GO in each of the procedures, as I was following an example. I was going to do it with the master stored procedure as well. If there is some helpful rule here, I'd appreciate the info.
Thanks.
August 20, 2008 at 11:14 am
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?
August 20, 2008 at 11:24 am
Kenena Klosson (8/20/2008)
Hello,I am creating a stored procedure that will run 6 other stored procedures. Why do you say not to use the BEGIN/END. Also where does GO play a part for this? Each of the procedures has updates for different tables, and I put a BEGIN and END followed by a GO in each of the procedures, as I was following an example. I was going to do it with the master stored procedure as well. If there is some helpful rule here, I'd appreciate the info.
Thanks.
You cannot use GO in the body of a stored procedure. Any GO's you see in templates are AFTER the stored procedure is Created or Altered, signifying the end of the batch.
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 20, 2008 at 11:30 am
I am not sure if I understand your question, but I will try to answer.
Regarding the do not use BEGIN and END:
It just applies to the this case:
[font="Courier New"]CREATE myStoredProc
AS
BEGIN
- --some code
END[/font]
GO
If you do this, then normally the entire has to be indented by one step, which you would not need to do if you simply wrote
[font="Courier New"]CREATE myStoredProc
AS
some code[/font]
BY ALL MEANS, DO use BEGIN and END blocks where they are needed. For instance,
[font="Courier New"]CREATE myStoredProc
AS
-- notice no "begin" here
DECLARE @li_ReturnCode int
SET @li_ReturnCode = -1
-- -----------------------------------------------------
-- STEP 1 - CALL 1st STORED PROC .. (SAY WHAT IT DOES)
-- -----------------------------------------------------
BEGIN
EXEC @li_ReturnCode = sp_No_1 -- the called stored proc MUST return a value
IF @li_ReturnCode <> 0 BEGIN
SET @li_Main_Return_Code = -100 + @li_ReturnCode
IF @li_Main_Return_Code <> 0 RETURN @li_Main_Return_Code
END
END
-- -----------------------------------------------------
-- STEP 2 - CALL 2nd STORED PROC .. (SAY WHAT IT DOES)
-- -----------------------------------------------------
BEGIN
EXEC @li_ReturnCode = sp_No_2 -- the called stored proc MUST return a value
IF @li_ReturnCode <> 0 BEGIN
SET @li_Main_Return_Code = -200 + @li_ReturnCode
IF @li_Main_Return_Code <> 0 RETURN @li_Main_Return_Code
END
END
and so on.
-- or do it this way --
BEGIN
EXEC @li_ReturnCode = sp_No_1 -- the called stored proc MUST return a value
IF @li_ReturnCode <> 0 BEGIN
SET @li_Main_Return_Code = -100 + @li_ReturnCode
IF @li_Main_Return_Code <> 0 GOTO AB_END
END
END
----
AB_END:
ROLLBACK TRAN -- if this code is executed within a transaction
RETURN @li_Main_Return_Code
-- notice no final "end" (matching the the "begin" which is not necessary after the AS)
GO
[/font]
The BEGIN ... END blocks allow you to have multiple lines of code. But even when there is only one line of code, which does not required the begin - end block, I sometimes use it when I feel it makes the code clearer. (subjective).
Note that I also use
[font="Courier New"]EXEC @li_ReturnCode = sp_No_1 -- the called stored proc MUST return a value
IF @li_ReturnCode <> 0 BEGIN
SET @li_Main_Return_Code = -100 + @li_ReturnCode
IF @li_Main_Return_Code <> 0 RETURN @li_Main_Return_Code
END
EXEC @li_ReturnCode = sp_No_2 -- the called stored proc MUST return a value
IF @li_ReturnCode <> 0 BEGIN
SET @li_Main_Return_Code = -200 + @li_ReturnCode
IF @li_Main_Return_Code <> 0 RETURN @li_Main_Return_Code
END[/font]
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply