August 31, 2022 at 3:02 pm
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
Kindest Regards,
Just say No to Facebook!August 31, 2022 at 3:20 pm
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.
August 31, 2022 at 3:32 pm
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
August 31, 2022 at 4:31 pm
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!August 31, 2022 at 4:51 pm
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
September 1, 2022 at 2:05 am
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
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy