Post reply

Execs and temporary tables

  • July 6, 2012 at 11:50 am

    #1509830

    Thanks for the question! but the set of possible answers was "too easy": once I saw that B#2 was going to error out while B#3 was not, I didn't look at the subsequent Bs :-).

  • July 6, 2012 at 12:50 pm

    #1509867

    mtassin (7/6/2012)

    Always nice to have an easy one on Friday....

    I concur... but what is easy for one person isn't for another.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • July 7, 2012 at 7:49 am

    #1509963

    BrainDonor (7/6/2012)

    That explanation requires expanding slightly, because there is more than one type of temporary table.

    Local temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a Global temporary table and the table can be accessed outside of the exec statement that created it.

    You are right. But with global temporary table this will be no fun.


    See, understand, learn, try, use efficient
    © Dr.Plch

  • July 7, 2012 at 7:51 am

    #1509964

    Michael Poppers (7/6/2012)

    Thanks for the question! but the set of possible answers was "too easy": once I saw that B#2 was going to error out while B#3 was not, I didn't look at the subsequent Bs :-).

    Thanks.

    I wanted to have it uniform.


    See, understand, learn, try, use efficient
    © Dr.Plch

  • July 7, 2012 at 7:52 am

    #1509965

    Thanks to everyone.

    Sorry, I missed the beggining of this discussion as I was out of civilization (holiday).


    See, understand, learn, try, use efficient
    © Dr.Plch

  • July 7, 2012 at 7:57 am

    #1509967

    bitbucket-25253 (7/6/2012)

    Emphasis (bolding) added by this poster

    BrainDonor (7/6/2012)

    That explanation requires expanding slightly, because there is more than one type of temporary table.

    Local temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a Global temporary table and the table can be accessed outside of the exec statement that created it.

    At the completion of the command given in the QOD

    exec ('create table #qotd2 (id int))' follows the above since the exec command completes, that is #qotd2 has gone out of scope.

    To illustrate, the code below has been modified so that the entire sequence of commands is contained within the scope of the exec command.

    exec ('create table #qotd2 (id int)

    alter table #qotd2 add i int

    insert into #qotd2 (i) values (2)

    select * from #qotd2

    drop table #qotd2')

    The above then will return for the SELECT statement the values:

    id i

    NULL 2

    I must agree. It's the case of B#7.


    See, understand, learn, try, use efficient
    © Dr.Plch

  • July 7, 2012 at 7:59 am

    #1509968

    WayneS (7/6/2012)

    mtassin (7/6/2012)

    Always nice to have an easy one on Friday....

    I concur... but what is easy for one person isn't for another.

    I'm surprised, there are someone (9% now) who choosed the "Every batch throws an error" answer. Curious.


    See, understand, learn, try, use efficient
    © Dr.Plch

  • July 9, 2012 at 12:28 am

    #1510182

    Thanks for the question.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 9, 2012 at 1:15 am

    #1510192

    Good Question !!!!!!.

  • July 9, 2012 at 1:36 am

    #1510201

    David Harder (7/6/2012)

    This question made up for yesterday's beating. Glad we ending the week with this one.

    See I missed this on Friday so was still busy chewing some sour grapes! However, this week's starting out on a high note (despite the miserable British summer!) 😎

  • July 9, 2012 at 4:43 am

    #1510248

    unfortunately, i select the wrong one. :doze:

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • July 10, 2012 at 7:58 am

    #1510932

    Good question, thanks.

  • July 18, 2012 at 6:23 am

    #1514165

    Very good question. I write code fairly often and it seems there is more to think about in SQL about scope than many programming languages. Either that or I just think about it more with programming.

  • July 18, 2012 at 6:45 am

    #1514176

    Mike Palecek (7/18/2012)

    Very good question. I write code fairly often and it seems there is more to think about in SQL about scope than many programming languages. Either that or I just think about it more with programming.

    Thank you. That's it.


    See, understand, learn, try, use efficient
    © Dr.Plch

  • July 18, 2012 at 10:08 am

    #1514330

    Nice easy question.

    But obviously batch 1 works, and batch 2 can't work (because the table is dropped by the exit from exec), and only one of the answers permits that; a different set of answer options could have made it necessary to look and see what the other batches did, which would perhaps have made it a better (although somewhat more tedious) question.

    Tom

Viewing 15 posts - 16 through 30 (of 31 total)

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