How to use temporary table in function

  • 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

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

  • 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

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



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

  • 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!

  • 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

  • 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

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

  • 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?

  • 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!
  • 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

  • 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

  • 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.


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

  • 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.


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

  • 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.


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

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

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