locked
Can I pass a table function parameter like this? RRS feed

  • Question

  • This works. Notice I am passing the required table function parameter using the declared variable.

    DECLARE @Date DATE = '2014-02-21'
    SELECT
    h.*, i.SomeColumn
    FROM SomeTable h
    LEFT OUTER JOIN SomeTableFunction(@Date) I ON i.ID = h.ID
    WHERE h.SomeDate = @Date
    

    But I guess you can't do this?... because I'm getting an error saying h.SomeDate cannot be bound. Notice in this one, I am attempting to pass in the table function parameter from the SomeTable it is joined to by ID.

    DECLARE @Date DATE = '2014-02-21'
    SELECT
    h.*, i.SomeColumn
    FROM SomeTable h
    LEFT OUTER JOIN SomeTableFunction(h.SomeDate) I ON i.ID = h.ID
    WHERE h.SomeDate = @Date
    
    Saturday, February 22, 2014 12:54 AM

Answers

  • You need to use OUTER APPLY here, e.g.

    DECLARE @Date DATE = '20140221'
    SELECT
    h.*, i.SomeColumn
    FROM SomeTable h
    OUTER APPLY SomeTableFunction(h.SomeDate) I 
    WHERE h.SomeDate = @Date


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Sunday, February 23, 2014 2:18 AM
    • Proposed as answer by Elvis Long Friday, February 28, 2014 7:12 AM
    • Marked as answer by Elvis Long Monday, March 3, 2014 10:03 AM
    Sunday, February 23, 2014 2:18 AM

All replies

  • Quick context on the above.

    SomeTableFunction(@Date) is actually an table function that acts on a Type II dimension table. So, basically, it returns the attributes for all SomeCodes that were active on @Date.

    SomeTable is a fact table that is keyed off SomeCode and I want the SomeDate on the fact table to drive what comes back from the table function.

    Now, this works if I am working with a specific SomeDate in the fact table because, as you can see, I can pass that specific Date value into the table function. But what if I wanted to work with a rage of SomeDate in the fact table?

    One way I can think of is to run the output of the table function into temp table for each SomeDate in the SomeTable and have the SomeTable join to that temp table on ID and SomeDate.

    I hope you followed that. Otherwise, if this is confusing, forget about it and please try to focus on the original question.

    Saturday, February 22, 2014 1:05 AM
  • >> Notice I am passing the required table function parameter using the declared variable. <<

    This is awful code, even for a skeleton. There is no generic “id” in RDBMS. We need a specific entity identifier for a specific entity, not a vague generic as you have done. SQL programers do not use procedural functions as you have done. 

    We do not use procedural functions in a functional/declarative language! ur whole approach is wrong. 

    You do not understand SQL as a language. I am going Zen on you, but if you learn, you will be grateful, grasshopper.  

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, February 22, 2014 1:41 AM
  • >> Notice I am passing the required table function parameter using the declared variable. <<

    This is awful code, even for a skeleton. There is no generic “id” in RDBMS. We need a specific entity identifier for a specific entity, not a vague generic as you have done. SQL programers do not use procedural functions as you have done. 

    We do not use procedural functions in a functional/declarative language! ur whole approach is wrong. 

    You do not understand SQL as a language. I am going Zen on you, but if you learn, you will be grateful, grasshopper.  

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    You specifically referenced this part of my original question, >> Notice I am passing the required table function parameter using the declared variable. <<

    How else are you going to use a table function? I don't give a crap who you are. Based on the way you responded, you totally discredited all the junk you have in your sig. What's the point of having all that book knowledge when you can't communicate? If you think I am using a table function in a way that isn't recommended, then why don't you explain? I know the difference between a set based logic and a procedural logic. If you're going to respond to a question, you need to dial back your SQL sensibilities and try to engage with some manners.

    To forum mods, can you please take this retard off my question. In fact please block him from other future responses to my question, if you can do that.

    Saturday, February 22, 2014 5:29 AM
  • Hi

    NO you cant pass a table function parameter like this?

    As When you declare @date assign value to it and pass as a parameter it will return table which you can use for join as you did it in first code 

    But when you pass date from some other table for generating table from your funtion it doesnt have date as it is not available there

    Ref :

    http://www.codeproject.com/Articles/167399/Using-Table-Valued-Functions-in-SQL-Server

    http://technet.microsoft.com/en-us/library/aa214485(v=sql.80).aspx

    http://msdn.microsoft.com/en-us/library/ms186755.aspx

    https://www.simple-talk.com/sql/t-sql-programming/sql-server-functions-the-basics/

    http://www.sqlteam.com/article/intro-to-user-defined-functions-updated

    Mark as answer if you find it useful


    Shridhar J Joshi Thanks a lot

    Saturday, February 22, 2014 6:27 AM
  • You can do it, See the apply operator 

    http://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

    DECLARE @Date DATE = '2014-02-21'
    SELECT
    h.*, i.SomeColumn
    FROM SomeTable h
    OUTER APPLY SomeTableFunction(h.SomeDate) I ON i.ID = h.ID
    WHERE h.SomeDate = @Date


    Satheesh
    My Blog | How to ask questions in technical forum


    Saturday, February 22, 2014 7:54 AM
  • you should be using this

    http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, February 22, 2014 8:56 AM
  • You do not understand SQL as a language.

    Well, your posts has shown a multiple times that your understanding of T-SQL and real-world database applications is minimal.

    You are not even able to tell actual code from quick mockups for the sake of the example. (Which it was clear that GoodOldFashionedLoverBoy was using.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 22, 2014 11:06 AM
  • You do not understand SQL as a language.

    Well, your posts has shown a multiple times that your understanding of T-SQL and real-world database applications is minimal.

    You are not even able to tell actual code from quick mockups for the sake of the example. (Which it was clear that GoodOldFashionedLoverBoy was using.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thanks Erland. You understood my mock up code as well as the others that responded, except for this bald headed pretentious retard.

    And thanks to someone else above who responded with the Cross Apply syntax. Now, this is coming back to me. Although I seem to recall certain performance issue with tables function and cross apply. I will have to proceed with caution.

    Saturday, February 22, 2014 2:39 PM
  • Although I seem to recall certain performance issue with tables function and cross apply. I will have to proceed with caution.

    One should be very careful with extrapolating observations from one situation to another case. It's easy to draw conclusions from what you see directly, that is the syntax in the query, but the real cause may lay with actual indexes at hand.

    That said, multi-statement table-valued functions are often involved with performance problems, because the optimizer has very little idea of how may rows they will return and how expensive they are. There is no such issue with inline table functions.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 22, 2014 3:26 PM
  • You need to use OUTER APPLY here, e.g.

    DECLARE @Date DATE = '20140221'
    SELECT
    h.*, i.SomeColumn
    FROM SomeTable h
    OUTER APPLY SomeTableFunction(h.SomeDate) I 
    WHERE h.SomeDate = @Date


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Sunday, February 23, 2014 2:18 AM
    • Proposed as answer by Elvis Long Friday, February 28, 2014 7:12 AM
    • Marked as answer by Elvis Long Monday, March 3, 2014 10:03 AM
    Sunday, February 23, 2014 2:18 AM