How to use temporary table in function

  • SQLTreeo

    SSC Enthusiast

    Points: 177

    Comments posted to this topic are about the item How to use temporary table in function

    __________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In

  • Carlo Romagnano

    SSC-Insane

    Points: 21985

    I like dirty "solutions". In extreme cases, I know that a way exists. 😀

  • Phil Factor

    SSC-Insane

    Points: 20074

    An interesting technique for sidestepping checks to see whether a function is deterministic. I'd love to see a practical example where this would be a good solution.

    Best wishes,
    Phil Factor
    Simple Talk

  • honza.mf

    SSCertifiable

    Points: 5519

    Nice one. I hope I remember it the moment I (or someone else) will need it.



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

  • RichB

    SSCrazy Eights

    Points: 9651

    Presumably that also has the effect of turning the temporary table into a global temporary table that magically disappears as soon as the original session clears it out? :hehe:

    Bet that could make for some troublesome bughunts.... and some extremely awkward concurrency issues!

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    I'd like to see an example on when to use that.

    But the thing is, a function is allowed to output a non-deterministic result. It just doesn't support temporary tables which is a different matter. And it doesn't allow side-effects.

    Try this:

    CREATE FUNCTION dbo.FN_WhichDateIsToday()

    RETURNS DATETIME

    AS

    BEGIN

    RETURN(SELECT GETDATE());

    END;

    Thank you for the small hint. 🙂

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • SQLTreeo

    SSC Enthusiast

    Points: 177

    Practical example behind this article was following:

    I needed to share data among stored procedures because I had very complex task which required to pull data from fixed tables to some temporary structures and do some logic above them. I picked #table solution for sharing data because all others (output parameters, UDDT, ...) were not sufficient for my scenario. Then I've started to "encapsulate" this complex logic and realized that you cannot use #table with function. I made up synonym workaround at this point.

    At the end of a day I had to use process-keyed tables for data sharing because this workaround had very big maintenance drawback as described in article.

    Jakub Dvorak

    __________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    if you need to return a table, why not just use a view?

  • Greg Webb

    SSC Rookie

    Points: 27

    CREATE FUNCTION dbo.FN_WhichDateIsToday()

    RETURNS DATETIME

    AS

    BEGIN

    RETURN(SELECT GETDATE());

    END;

    That's interesting - I'd always previously used a view which presented GetDate when I needed that. Hadn't occurred to me to check if the restriction was still in place 🙂

    I confess I'm still a little confused why this trick is necessary rather than just using a table variable inside the function, which is permitted?

  • rfr.ferrari

    SSCertifiable

    Points: 6879

    very nice!!!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • jbnv

    SSC Eights!

    Points: 973

    I confess I'm still a little confused why this trick is necessary rather than just using a table variable inside the function, which is permitted?

    Mr. Dvorak stated that he is sharing data among multiple stored procedures. I say that if you need to share data among multiple objects, you should put it in a named permanent table.

    When I saw this article in the newsletter, my guess was that he'd tell us to use a CTE instead of a temporary table.

    Jay Bienvenu | http://bienv.com | http://twitter.com/jbnv

  • laurah

    SSC Journeyman

    Points: 99

    Why not just use: ???

    CREATE FUNCTION funcName1 ()

    RETURNS @tblResult TABLE

    (Num1 INT,

    Num2 INT,

    Num3 INT)

    AS BEGIN

    INSERT @tblResult

    (Num1, Num2, Num3)

    SELECT 100, 200, 300

    RETURN

    END

  • Jeff Moden

    SSC Guru

    Points: 996810

    RichB (7/21/2011)


    Presumably that also has the effect of turning the temporary table into a global temporary table that magically disappears as soon as the original session clears it out? :hehe:

    Bet that could make for some troublesome bughunts.... and some extremely awkward concurrency issues!

    No... it wouldn't turn the temp table into a global one anymore than using a call to a temp table from a stored procedure that didn't actually build the temp table, so no concurrency issues.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996810

    Jakub Dvorak (7/20/2011)


    Comments posted to this topic are about the item <A HREF="/articles/UDF/74231/">How to use temporary table in function</A>

    +10 for thinking outside of the box, Jakub. There have been a couple of times (and, no, I can't think of what they are, just now) where I'd have preferred to use a Temp Table instead of a Table Variable in a function. I've known this could be done with stored procedures (without the use of a Synonym) and, for some reason, never thought about doing it in a function by using a Synonym like you did. Well done.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996810

    jbnv (7/21/2011)


    I confess I'm still a little confused why this trick is necessary rather than just using a table variable inside the function, which is permitted?

    Mr. Dvorak stated that he is sharing data among multiple stored procedures. I say that if you need to share data among multiple objects, you should put it in a named permanent table.

    That would either destroy the ability for concurrent runs (as would the use of a global temp table) or require the use of dynamic SQL for differently named permanent (or global temp) tables to all concurrency.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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