Post reply

Table-Valued Functions in Stored Procedures

  • October 11, 2012 at 10:10 am

    #263749

    I am writing a sproc for a report. The sproc can accept a series of criteria (~5). The easiest way (for development/maintenance/troubleshooting) I have found to do this is to create a Table-Valued Function (tvf) for each of the criteria. Each tvf returns only a single column with the ID #.

    The criteria are a bit too complex to use in the WHERE clause of the main sproc. This is why i created separate tvf's.

    In the sproc, I then join (on the ID #) all of these tvf’s to the main table which returns all the columns that will appear in the report.

    Is there a better way to do this for performance?

    Thanks.

  • October 11, 2012 at 10:22 am

    #1548258

    Can you post an example of one of the TVF's?

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/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]

  • October 11, 2012 at 10:33 am

    #1548266

    Hopefully this illustrates the point. This tvf takes 1 arg which is an OrganizationID (which is passed from the sproc into the tvf). if the user wants to see a single Org, they select it from a list and that ID is passed. if they want to see all then 0 is passed.

    the sproc can call mutliple of these types of tvf's, all in a very similar way.

    ALTER FUNCTION [dbo].[udf_frmMaterialInventoryOutgoing_GIK_Result_Org]

    (

    @OrgID int

    )

    RETURNS TABLE

    AS

    RETURN

    (

    SELECT MaterialInventoryOutgoingID

    FROM MaterialInventoryOutgoing

    WHERE (@OrgID = 0) OR

    (@OrgID <> 0) AND (DestinationOrganizationID = @OrgID)

    )

  • October 12, 2012 at 12:17 am

    #1548451

    Sounds like you need a 'catch-all' query

    Try this post :http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/


    Clear Sky SQL
    My Blog[/url]

  • October 12, 2012 at 8:07 am

    #1548658

    The perfect answer for my situation. Looks like i have some code to rewrite. Thank you.

  • October 17, 2012 at 7:28 am

    #1549995

    Also try using inline table value functions instead of multi-statement table value functions.

    they're much faster.

    http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/

    http://www.sqlservercentral.com/blogs/discussionofsqlserver/2012/02/15/comparing-inline-and-multistatement-table-valued-functions/


    If you need to work better, try working less...

Viewing 6 posts - 1 through 5 (of 5 total)

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