Temporary tables SCOPE

  • Carlo Romagnano

    SSC-Insane

    Points: 21876

    Comments posted to this topic are about the item Temporary tables SCOPE

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71617

    Nice question, thanks Carlo.
    enjoyed  the red herrings in the options...
    the "DROP TABLE IF EXISTS #Table" is very handy, too

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • BillLudlow

    SSCertifiable

    Points: 6446

    Drat! Read it totally wrong and wanted to select 3 answers, then picked the wrong 2.

  • allinadazework

    SSCarpal Tunnel

    Points: 4365

    Good question, thanks. Made me really think hard! Good stuff!

  • srienstr

    SSCrazy

    Points: 2366

    Heh, I was so confident of my answer being "obviously" correct that I expected to find out that I was wrong about how EXECUTE() functions.


    Puto me cogitare, ergo puto me esse.
    I think that I think, therefore I think that I am.

  • Revenant

    SSC-Forever

    Points: 42467

    BillLudlow - Tuesday, September 5, 2017 1:26 AM

    Drat! Read it totally wrong and wanted to select 3 answers, then picked the wrong 2.

    The same. Thanks, Carlo!

  • Lynn Pettis

    SSC Guru

    Points: 442235

    Why do I feel that this was a very easy question?

  • Revenant

    SSC-Forever

    Points: 42467

    Lynn Pettis - Tuesday, September 5, 2017 4:02 PM

    Why do I feel that this was a very easy question?

    Because you are who you are, my friend.

  • Ed Wagner

    SSC Guru

    Points: 286969

    Lynn Pettis - Tuesday, September 5, 2017 4:02 PM

    Why do I feel that this was a very easy question?

    Agreed.  I was looking for the trick, but it was just the very important point about scope.  It may sound scary, but I use the approach covered in the second batch frequently.  It's very handy when I can't know the return column names when they're built dynamically.

    Nice question, Carlo.  Thanks.

  • ChrisCarsonSQL

    SSChasing Mays

    Points: 604

    I must be missing something because I tried to execute this SQL and all I received was error 156 incorrect syntax near the keyword 'if'

    _____________________________

    Past performance != future results.
    All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...

  • Lynn Pettis

    SSC Guru

    Points: 442235

    ChrisCarsonSQL - Thursday, September 7, 2017 4:35 PM

    I must be missing something because I tried to execute this SQL and all I received was error 156 incorrect syntax near the keyword 'if'

    What version of SQL Server are using?  IIRC, the syntax used in this question is for SQL Server 2016 and newer.

  • Carlo Romagnano

    SSC-Insane

    Points: 21876

    ChrisCarsonSQL - Thursday, September 7, 2017 4:35 PM

    I must be missing something because I tried to execute this SQL and all I received was error 156 incorrect syntax near the keyword 'if'

    What sql server version are you using?
    (try with sql server 2016)

  • Avi1

    SSCrazy

    Points: 2313

    ChrisCarsonSQL - Thursday, September 7, 2017 4:35 PM

    I must be missing something because I tried to execute this SQL and all I received was error 156 incorrect syntax near the keyword 'if'

    Only first line will not work in the prior versions, it will work only from 2016 version. But main code to related to temp table will work, you can drop the temp table the way you drop normal tables, or just close the window in SSMS

  • ChrisCarsonSQL

    SSChasing Mays

    Points: 604

    Lynn Pettis - Thursday, September 7, 2017 4:39 PM

    ChrisCarsonSQL - Thursday, September 7, 2017 4:35 PM

    I must be missing something because I tried to execute this SQL and all I received was error 156 incorrect syntax near the keyword 'if'

    What version of SQL Server are using?  IIRC, the syntax used in this question is for SQL Server 2016 and newer.

    That was the issue.  Thank you!

    _____________________________

    Past performance != future results.
    All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...

  • Christian4145

    SSC Rookie

    Points: 36

    Just to add:

    There is different behavior on SQL-Server 2012. I have to change the statement to (there is no 'if exists')


    if object_id ('tempdb..#t') is not null drop table #t
    execute ('create table #t (i int, z int)')
    select * from #t
    GO
    -- second Batch
    if object_id ('tempdb..#t') is not null drop table #t
    create table #t (i int)
    execute ('alter table #t add z int')
    select * from #t

    There is an error on SQL-Server 2012:

    Msg 208, Level 16, State 0, Line 3
    Invalid object name '#t'.

    The resut will be i,z.

    Learning buy doing.

Viewing 15 posts - 1 through 15 (of 17 total)

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