Return

  • Mike Dougherty-384281

    SSCrazy

    Points: 2764

    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?

  • Thomas Abraham

    SSChampion

    Points: 10761

    Nice basic question to start the week. Thanks for your contribution.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • twin.devil

    SSC-Insane

    Points: 22208

    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 .. 🙁

  • kevin.l.williams

    SSCarpal Tunnel

    Points: 4916

    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.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Carlo Romagnano

    SSC-Insane

    Points: 22025

    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 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.

    +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.

  • Robert-378556

    SSCertifiable

    Points: 5542

    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.

  • Mike Dougherty-384281

    SSCrazy

    Points: 2764

    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 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).

    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.

  • HK Sarvaiya

    SSC Enthusiast

    Points: 134

    Don't select statement "SELECT * FROM @TABLE WHERE VAL > 1;" should be inside procedure statements.

  • Abrar Ahmad_

    SSCarpal Tunnel

    Points: 4223

    [font="Verdana"]gotcha! ;-)[/font]

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    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 !!!

  • venkat.ecengg 68608

    SSC Journeyman

    Points: 84

    No possible result set the procedure can return.

  • jfgoude

    SSCrazy

    Points: 2586

    read carefully and answer

    +1

    no problem remaining is just bad looser diatrib

Viewing 13 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply