Post reply

Return

  • July 17, 2013 at 12:33 pm

    #1633413

    Nice, very nice. Thanks, Carlo!

  • July 17, 2013 at 2:18 pm

    #1633443

    Interesting question thanks:-)

    I think it also provides a good argument for the use of statement terminators or formatting scripts in a clear manor (with comments:-D).

    Batch one appears to have 3 statements rather than 4, due to the way it is formatted. If it had been written as select 1;

    return; select 2;

    select 3; or

    --First Query

    select 1

    -- Exit Batch

    return

    -- Other queries

    select 2

    select 3 it would be very clear that 'select 2' is not part of the return statement.

  • July 17, 2013 at 4:44 pm

    #1633476

    Primo Dang (7/17/2013)

    L' Eomot Inversé (7/17/2013)

    The reason that you can't use return with a value in a batch is that batches don't return scalar values, they return (possibly empty) result sets or they don't return anything or they raise an error.

    Thanks for the explanation, Tom. I had no idea batches could return anything at all.

    Oops, I got that wrong; the first of the three things they can return is a set of possibly empty result sets, not necessarily just one possibly empty result set. They have to be able to do that, or embedded SQL (including proc calls from outside SQL) would not be very useful.

    Also, thank you Carlo for the question. I loved the trick with non-existing table! 😀

    Yes, I liked that too. Definitely fun, and also definitely something that will help people understand what RETURN does.

    edit: add a closing ] to a quote tag

    Tom

  • July 17, 2013 at 4:51 pm

    #1633477

    mickyT (7/17/2013)

    Interesting question thanks:-)

    I think it also provides a good argument for the use of statement terminators or formatting scripts in a clear manor (with comments:-D).

    Batch one appears to have 3 statements rather than 4, due to the way it is formatted. If it had been written as select 1;

    return; select 2;

    select 3; or

    --First Query

    select 1

    -- Exit Batch

    return

    -- Other queries

    select 2

    select 3 it would be very clear that 'select 2' is not part of the return statement.

    Yes, that's right (apart from the crazy idea that statement terminators have any place in a properly designed language - although maybe that's irrelevant, because SQL certainly isn't such a language and statement terminators maybe do have a place there, since the terribleness of T-SQL has now increased to the point where it actually needs them. But in my experience it's necessary to be able to interpret SQL that is badly laid out and doesn't have comments that indicate what is going on. It's sad, but when you find yourself looking after a database which someone who has not been educated properly about relational principles and general development principles your are going to have a lot of code like that that you need to get to understand, so questions using such horribly written SQL are actually a good thing, because they help us recognise that this is something we will have to cope with and give us practise in coping with it.

    Tom

  • July 17, 2013 at 11:57 pm

    #1633523

    mickyT (7/17/2013)

    Interesting question thanks:-)

    I think it also provides a good argument for the use of statement terminators or formatting scripts in a clear manor (with comments:-D).

    Batch one appears to have 3 statements rather than 4, due to the way it is formatted. If it had been written as select 1;

    return; select 2;

    select 3; or

    --First Query

    select 1

    -- Exit Batch

    return

    -- Other queries

    select 2

    select 3 it would be very clear that 'select 2' is not part of the return statement.

    Another use of RETURN in a batch is with IF:

    SELECT 1

    -- only on test db

    IF DB_NAME() NOT LIKE '%_TEST'

    BEGIN

    print 'WARNING: run only on test db'

    RETURN

    END

    ALTER TABLE MY_TBL ADD ID_TEST INT NULL

    -- follows other stmts

  • July 18, 2013 at 1:14 am

    #1633545

    Note to self - read question clearly before answering, including how many choices to make.....doh!!! Not a good start to the day - I knew the answer but only selected one option.....:w00t:

  • July 19, 2013 at 7:24 am

    #1634014

    nice question.. new things added to my database...

    Manik
    You cannot get to the top by sitting on your bottom.

  • July 22, 2013 at 8:58 am

    #1634506

    Nice question

    Thanks

  • July 22, 2013 at 9:18 am

    #1634516

    Good one.. thanks for posting.

    -RP

  • July 29, 2013 at 2:16 am

    #1636547

    Nice question. Good information about return functionality. 🙂

  • August 29, 2013 at 8:45 am

    #1646016

    very nice thanks

    especially "RETURN SELECT * FROM non_existing_table" !!

Viewing 11 posts - 16 through 25 (of 25 total)

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