Post reply

Table valued function where parameter has multiple values

  • January 29, 2015 at 5:36 am

    #297288

    Is it possible to pass multiple values to a TVF, such as using an IN clause?

  • January 29, 2015 at 5:41 am

    #1773947

    you can pass a table value parameter, which you have to define first, and then you can use it.

    https://msdn.microsoft.com/en-us/library/bb510489.aspx

    you can also pass a delimited string, and use a function to split it into it's pieces inside the function.

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 29, 2015 at 5:53 am

    #1773952

    Thanx.

  • January 29, 2015 at 6:18 am

    #1773959

    Here is something from the Microsoft website. Am I missing something?

    There are several limitations to table-valued parameters:

    •You cannot pass table-valued parameters to CLR user-defined functions.

  • January 29, 2015 at 6:32 am

    #1773962

    a CLR is a common language runtime object, which is not the same as a SQL inline table vlaue function, scalar function, or multi statement table function.

    so while a CLR function canot use a table value parameter , any SQL function you identified would be fine

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 29, 2015 at 6:36 am

    #1773964

    Thanx.

  • January 29, 2015 at 5:31 pm

    #1774215

    Lowell (1/29/2015)

    you can pass a table value parameter, which you have to define first, and then you can use it.

    https://msdn.microsoft.com/en-us/library/bb510489.aspx

    Just watch out for this weird bug:

    http://www.sqlservercentral.com/Forums/Topic1653333-392-1.aspx#bm1653437

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

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

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