August 19, 2013 at 7:36 am
I don't understand the point of this QotD.
I understand the cleverly-contrived illustration code. However, I'd never want to see anyone write code like that for my production environment. If this much confusion is caused by the simplest example to illustrate the issue, what problems are caused by early-exit in real world complexity?
I think I'll continue with the belief that "return" belongs at the end of a procedure. If I ever see anyone in my group using return in a "wrong" way, I'll require either a rewrite or a paragraph of commenting to explain why the potentially confusing construct is used.
btw, if return is encountered while a transaction is uncommitted - does it rollback?
August 19, 2013 at 8:32 am
Thanks everyone for the comments
"Mike Dougherty" its a funny world, anything can happen !!!
"btw, if return is encountered while a transaction is uncommitted - does it rollback?" And the answer is NO 🙂
Transaction remains as it is .. 🙁
August 19, 2013 at 9:53 am
Rune Bivrin (8/19/2013)
Got it. But to be precise there won't even be a result set. Not even one with zero rows.
Thought I would see more comments like this. This can be a critical distinction in application development.
August 19, 2013 at 10:00 am
Mike Dougherty-384281 (8/19/2013)
I think I'll continue with the belief that "return" belongs at the end of a procedure. If I ever see anyone in my group using return in a "wrong" way, I'll require either a rewrite or a paragraph of commenting to explain why the potentially confusing construct is used.
I often use RETURN to exit from predefined error conditions. Which are usually checked at the start of the procedure.
IF @DateArg IS NULL
BEGIN;
RAISERROR ('Date must be supplied', 16, 1);
RETURN -1;
END;
I think that makes my code a lot more readable then the allternative (which would be to nest the entire actual logic of the procedure within the ELSE - resulting in a high indentation level if multiple conditions have to be checked).
btw, if return is encountered while a transaction is uncommitted - does it rollback?
No. But there are some specific situations where SQL Server will force a rollback if it detects that the transaction count when exiting the stored procedure is not the same as when calling it.
August 19, 2013 at 3:24 pm
Hugo Kornelis (8/19/2013)
Mike Dougherty-384281 (8/19/2013)
I think I'll continue with the belief that "return" belongs at the end of a procedure. If I ever see anyone in my group using return in a "wrong" way, I'll require either a rewrite or a paragraph of commenting to explain why the potentially confusing construct is used.I often use RETURN to exit from predefined error conditions. Which are usually checked at the start of the procedure.
IF @DateArg IS NULLBEGIN;
RAISERROR ('Date must be supplied', 16, 1);
RETURN -1;
END;
I think that makes my code a lot more readable then the allternative (which would be to nest the entire actual logic of the procedure within the ELSE - resulting in a high indentation level if multiple conditions have to be checked).
btw, if return is encountered while a transaction is uncommitted - does it rollback?
No. But there are some specific situations where SQL Server will force a rollback if it detects that the transaction count when exiting the stored procedure is not the same as when calling it.
+1
In T-SQL, as any other language, you should use commands, statements, operators, functions and so on, with knowledge to meet your needs.
Why do you think that
"return" belongs at the end of a procedure
?
The procedure returns also without RETURN at the end.
August 19, 2013 at 5:43 pm
There is no resultset. Procedure returns just an integer. There is a huge difference between no resultset and empty resultset. Since there was no such answer, all answers are wrong. Only the return value can be converted into resultset, so least wrong answer would be one row.
Depending on tool (client lib, api sequence), the procedure may return 0 columns (no resultset) or 2 columns, but no rows (empty resultset).
Still, "no resultset" answer is missing.
August 19, 2013 at 5:51 pm
Hugo Kornelis (8/19/2013)
I often use RETURN to exit from predefined error conditions. Which are usually checked at the start of the procedure.
IF @DateArg IS NULLBEGIN;
RAISERROR ('Date must be supplied', 16, 1);
RETURN -1;
END;
I think that makes my code a lot more readable then the allternative (which would be to nest the entire actual logic of the procedure within the ELSE - resulting in a high indentation level if multiple conditions have to be checked).
I hadn't thought of that. Of course, that pattern should be easy to follow linearly from top to bottom: error? exit else keep checking. By the time you pass the gauntlet the body of the procedure likely isn't exiting in the middle of a loop. Though I imagine you're also not writing a lot of loopy T-SQL in the real world either.
Carlo Romagnano (8/19/2013)
In T-SQL, as any other language, you should use commands, statements, operators, functions and so on, with knowledge to meet your needs.Why do you think that
"return" belongs at the end of a procedure
?
The procedure returns also without RETURN at the end.
Because I have had to debug others' code with multiple exit points in a too-long procedure and that style leads to confusion - sometimes even on the part of the code author. I guess it's just a code convention issue. I use indented comma-first for fields list so when a comma is missing, it's immediately recognizable. I indent table names one per row to make them more visible and obvious. I use table alias even when there is only one table so I won't have to disambiguate the other clauses later. All these habits make it easier, not more difficult, to write procedures. I'm a strong believer in Making Wrong Code Look Wrong. I feel like early-exit the way it is done in this question has an unpleasant code smell, while the usage Hugo reminded me of (above) is a well-considered exception to my "rule." I suggested my team avoid early-exit as a general rule with the caveat that going against the convention is acceptable provided there is a comment explaining off-norm use. In the above example, I'd consider the raiserror messages self-documenting but a simple "heads up" note to the maintenance programmer might still be a nice courtesy.
August 19, 2013 at 10:21 pm
Don't select statement "SELECT * FROM @TABLE WHERE VAL > 1;" should be inside procedure statements.
August 20, 2013 at 3:56 am
[font="Verdana"]gotcha! ;-)[/font]
August 21, 2013 at 11:24 pm
sqlnaive (8/19/2013)
Ohh Gosh... Caught in the trap. Not a good start for the week. :sick:It was just another "be on your toes" QOTD. 🙂
+1.
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
September 25, 2013 at 3:33 pm
No possible result set the procedure can return.
October 1, 2013 at 2:28 am
read carefully and answer
+1
no problem remaining is just bad looser diatrib
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply