Post reply

Parameterized Table-Valued Function VS. Parameterized Stored Procedure - Selecting and returning data by value(s)

  • March 30, 2011 at 10:16 am

    #237056

    It seems as though there are pros and cons to using, depending on the context, table-valued functions (i.e., functions that return a table) and stored procedures. Though, in the context of a simple select statement that limits the data returned based on values passed into the fn/sproc, which method is more robust/useful and might be considered to be "best practice", if at all.

  • March 30, 2011 at 10:42 am

    #1305559

    It depends.

    Can you explain a bit more?

    -- Gianluca Sartori

  • March 30, 2011 at 10:56 am

    #1305569

    Well, I can tell you that the select statements will:

    1) be re-used several times (to process logic (i.e., w/in a sproc))

    2) return NO MORE THAN one row

    3) have one or more columns

    4) NOT be used to join on if embedded w/in a TVF

    5) NOT be used by an app/web app/etc.

    I personally am leaning towards putting them into a TVF and calling them from a SP to process logic. Performance appears to be a non-issue as they are both cached. TVFs are seemingly more flexible to use in this context from my point of view. Thoughts?

  • March 30, 2011 at 2:08 pm

    #1305677

    TVFs may be a bit more flexible from a programming perspective especially if you're interested in capturing the results in a temp table (re: limitations on INSERT...EXEC).

    If you choose TVFs, one piece of advice from a performance perspective...favor Inline TVFs, not Multi-statement TVFs.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 30, 2011 at 2:22 pm

    #1305697

    Agreed. Yes, I'm only using ITVFs - not MSTVFs for the same reason you mentioned.

  • March 30, 2011 at 4:00 pm

    #1305731

    Generally speaking, I would go with an iTVF.

    For a stored procedure, if it needs to be called for every row in a query, then you have no choice but to use RBAR.

    With an iTVF, you can utilize CROSS APPLY to run in a set-based manner.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • March 31, 2011 at 12:15 am

    #1305794

    I agree with Wayne, iTVFs are nice because you can actually SELECT from and JOIN (CROSS APPLY) to them, which you can't do with SPs.

  • March 31, 2011 at 3:50 am

    #1305874

    You can also use the iTVF just like a table (a paraterized view if you like). It all depends on what the iTVF does and is written.

  • March 31, 2011 at 6:59 am

    #1305937

    Thanks for the feedback. No RBAR-type queries will be executed w/these iTVFs - I'm typically against such queries except for a few isolated scenarios. I may use them in a JOIN/CROSS APPLY, though, at some point. These functions are more or less parameterized views that return a single-row result set. They're extremely convenient to use w/in the sprocs i'm building as, another poster mentioned, you can simply use a select statement to populate a variable w/in the sproc. You can do the same thing w/a sproc, but it's seemingly more complex and I'm not sure what, if any, value is added in using sprocs for this particular scenario. I'm curious to hear feedback from those who chose "Parameterized Stored Procedure" as their choice in the poll. Good stuff!

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

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