Dynamic SQL and TempTables

  • Thomas Franz

    Hall of Fame

    Points: 3535

    Comments posted to this topic are about the item Dynamic SQL and TempTables

    God is real, unless declared integer.

  • HappyGeek

    SSCoach

    Points: 18661

    Good question thank you.

    ...

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71320

    Nice question to end the week on, thanks Thomas

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

  • TomThomson

    SSC Guru

    Points: 104763

    A nice and amusing question. I rather like the explanation because it's thorough without being verbose - I wouldn't have made as good a job of it (I would have prbably gone down some pointless side-tracks).

    But rather a lot of lines of code code to digest, and it could have been raised exacly the same issues with only half the number of tables and insert statements, which would have been a more easily digestible version.

    Tom

  • George Vobr

    SSCrazy Eights

    Points: 8935

    Thanks Thomas for an interesting question and a good explanation.

  • dale_berta

    SSC Eights!

    Points: 919

    I get what he's trying to do, but procedures return an INT. This procedure never sets a return value, so it returns a 0.

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71320

    dale_berta - Friday, August 25, 2017 5:54 AM

    I get what he's trying to do, but procedures return an INT. This procedure never sets a return value, so it returns a 0.

    The procedure does indeed return an INT.
    However, the procedure produces multiple result sets.

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

  • Sean Lange

    SSC Guru

    Points: 286411

    TomThomson - Friday, August 25, 2017 5:23 AM

    A nice and amusing question. I rather like the explanation because it's thorough without being verbose - I wouldn't have made as good a job of it (I would have prbably gone down some pointless side-tracks).

    But rather a lot of lines of code code to digest, and it could have been raised exacly the same issues with only half the number of tables and insert statements, which would have been a more easily digestible version.

    I agree with both points here. I actually didn't do much to decipher all the code. I sort of skimmed the code and skipped to the answers. There was one and only one reasonable answer based on the temp tables being generated with dynamic sql so it was simple to answer.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • SQLRNNR

    SSC Guru

    Points: 281210

    Yet another question that turns us into code parsers. Less code would have been ideal on this one.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 9 posts - 1 through 9 (of 9 total)

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