Is It Possible to Join a Table-Valued Function w/Param To DB table

Question

Is It Possible to Join a Table-Valued Function w/Param To DB table

YSLGuru

SSC-Insane

Points: 21661
More actions
August 31, 2022 at 3:02 pm

#4082448
If I have a TVF (Table Valued Function) that accepts a date as a parameter I can join it to a Regular DB table if the value passed into the TVF is a fixed date so I can either use GetDate() or I can assign a date to a variable and pass the variable into the TVF and it works. What I want to know is if it possible to pass to the TVF a date value from a field in a table previously joined in the query? I'm guessing that's not possible since at that point it's not a singular value being passed to the TVF but in case someone has found a way to do this kind of thing I'm asking that Q here.
I know below is not the complete set of code to re-create every table and teh TVF but I'm not allowed to share that code (NDA) and this is really just a theoretical question, I'm asking not how to do this for this specific query but if such a thing is possible at all.
Thanks
EX: The TVF udfWAM_UNIT_STATUS is being passed in the date from MM2WO.dtCall
```
  FROM PROPERTY P Join UNIT U   ON P.hMy = U.hProperty
                  Join MM2WO WO ON U.hmy = WO.hUnit
                  JOIN dbo.udfWAM_UNIT_STATUS( Wo.dtCall ) USHC ON WO.hUnit = USHC.hUnit
```
- This topic was modified 2 years, 2 months ago by YSLGuru.
- This topic was modified 2 years, 2 months ago by YSLGuru.
Kindest Regards,
Just say No to Facebook!

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

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

why don't you try it? should be rather easy for you to set up a test case on your own instead of spending time asking if it is possible.

you even have a example already done so what was the outcome of executing that code?

and if you wish to know what happens to the value passed then create a simple function that returns the passed field back - could not be easier to test.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

Use CROSS APPLY instead of INNER JOIN

  FROM PROPERTY P 
 INNER Join UNIT U   
    ON P.hMy = U.hProperty
 INNER Join MM2WO WO 
    ON U.hmy = WO.hUnit
 CROSS APPLY dbo.udfWAM_UNIT_STATUS(Wo.dtCall) USHC 
 WHERE WO.hUnit = USHC.hUnit

YSLGuru SSC-Insane Points: 21661 More actions · Answer 3

If I try to use what I showed it fails. I get an error on WO.dtCall, says multi-part identifier could not be bound.

Thanks

Kindest Regards,

Just say No to Facebook!

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

YSLGuru wrote:

If I try to use what I showed it fails. I get an error on WO.dtCall, says multi-part identifier could not be bound.
Thanks

Try this:

 FROM PROPERTY P 
 INNER Join UNIT U   
    ON P.hMy = U.hProperty
 INNER Join MM2WO WO 
    ON U.hmy = WO.hUnit
 CROSS APPLY (SELECT * 
                FROM dbo.udfWAM_UNIT_STATUS(Wo.dtCall) USHC 
               WHERE WO.hUnit = USHC.hUnit) USHC

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

If your "TVF" has the word BEGIN in it, that'll make a world of hurt no matter how you're using it. That would make it an mTVF (Multi-statement Table Valued Function), which is usually worse than a scalar function.

--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.

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)