Creating and using inline table-valued functions

  • kimani_m

    SSC Enthusiast

    Points: 101

    Comments posted to this topic are about the item Creating and using inline table-valued functions

  • ChrisM@Work

    SSC Guru

    Points: 186078

    Excellent article, very thorough. Thanks for sharing this.

    [font="Arial"]โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • sqlfriend

    SSC Guru

    Points: 52374

    Thanks for the great article!  Very through and easy understand!

    Can someone change my rate to 5 stars instead of one, not sure how to use the rate star, when I clicked the first star without having time to click next, it jumped to the end, so it ended one star.

  • kimani_m

    SSC Enthusiast

    Points: 101

    Thank you <a title="Go to ChrisM@Works.

  • kimani_m

    SSC Enthusiast

    Points: 101

    Glad you found it easy to understand sqlfriends. Thank you.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182410

    Thanks for this nice write-up Kimani, good job!
    ๐Ÿ˜Ž

  • kimani_m

    SSC Enthusiast

    Points: 101

    Thanks Eirikur. Hope you found it useful.

  • Jeff Moden

    SSC Guru

    Points: 995652

    Agreed with the others.  Nice job on the article, Kimani .

    BTW/FYI... you CAN actually call a stored procedure from within a function using OPENROWSET.  Because of OPENROWSET (it won't take variables) and the inability to use dynamic SQL in a function (although I'll be someone even figured a way past that), it has some fairly limited uses but for those uses it works a treat.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • kimani_m

    SSC Enthusiast

    Points: 101

    Thank you Jeff and also, thank you for pointing out that you can certainly call a stored procedure within a function.
    I ran the following script on my Dev machine and it certainly worked a treat.

    USE StackOverflow2010;
    GO
    sp_configure 'show advanced options',1
    RECONFIGURE
    GO
    sp_configure 'Ad Hoc Distributed Queries',1
    RECONFIGURE
    GO
    CREATE OR ALTER PROCEDURE dbo.NoddyProc
    AS
    BEGIN
      SELECT 1 AS Num;
    END
    GO
    CREATE OR ALTER FUNCTION dbo.itvfnNoddyFunction ()
    RETURNS TABLE
    AS RETURN (
    SELECT np.Num
    FROM OPENROWSET('SQLOLEDB','Server=(local);Initial catalog=StackOverflow2010;Trusted_Connection=yes;',
          'EXECUTE dbo.NoddyProc') AS np
    );
    GO
    SELECT Num
    FROM dbo.itvfnNoddyFunction();
    GO

  • Jeff Moden

    SSC Guru

    Points: 995652

    kimani_m - Wednesday, January 23, 2019 7:36 AM

    Thank you Jeff and also, thank you for pointing out that you can certainly call a stored procedure within a function.
    I ran the following script on my Dev machine and it certainly worked a treat.

    USE StackOverflow2010;
    GO
    sp_configure 'show advanced options',1
    RECONFIGURE
    GO
    sp_configure 'Ad Hoc Distributed Queries',1
    RECONFIGURE
    GO
    CREATE OR ALTER PROCEDURE dbo.NoddyProc
    AS
    BEGIN
      SELECT 1 AS Num;
    END
    GO
    CREATE OR ALTER FUNCTION dbo.itvfnNoddyFunction ()
    RETURNS TABLE
    AS RETURN (
    SELECT np.Num
    FROM OPENROWSET('SQLOLEDB','Server=(local);Initial catalog=StackOverflow2010;Trusted_Connection=yes;',
          'EXECUTE dbo.NoddyProc') AS np
    );
    GO
    SELECT Num
    FROM dbo.itvfnNoddyFunction();
    GO

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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 10 posts - 1 through 10 (of 10 total)

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