SQL Server user defined function returns table -- cannot call it from select query

Question

I cannot get this type of select query (pseudo-code) to work. The UDF returns a table with 8 columns in a single row for a given 'UID_VEHICLE'. It works perfectly when the 'UID_VEHICLE' is provided as a constant like 3308. But I need one row of these function-results for each vehicle for a given customer -- up to 100 rows to be returned.

SELECT 
    * 
FROM
    [dbo].[fnGetNextDOT_InspectionData](UID_VEHICLE)
WHERE  
    UID_VEHICLE IN (SELECT UID_VEHICLE 
                    FROM tVEHICLES 
                    WHERE UID_CUSTOMER = 88);

Your comments and solutions are welcome...thanks...John

Solomon Rutzky · Accepted Answer · 2017-04-02 15:25:42Z

2

When passing row values from a query into a TVF, you need to use CROSS APPLY or OUTER APPLY (starting with SQL Server 2005):

SELECT   * -- or dot.*, or whatever is desired
FROM     tVEHICLES veh
CROSS APPLY [dbo].[fnGetNextDOT_InspectionData](veh.UID_VEHICLE) dot
WHERE    veh.UID_CUSTOMER = 88;

edited Apr 2, 2017 at 15:25

answered Apr 2, 2017 at 15:12

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

John D Over a year ago

EXCELLENT!!! This is the answer, but with minor change..SELECT dot.*... Thanks you very much -- you are very smart!!!

Solomon Rutzky Over a year ago

@JohnD Yer welcome, and I updated my answer with that. I didn't include it at first since I didn't know what columns you needed and figured you would change the SELECT as needed.

John D Over a year ago

@srutzky...dear sir...Although I marked this as the answer, and I thank you for your answer, I find upon further examination of the result-set, I set the CROSS to OUTER and found that NOT all vehicles for customer=88 are being returned. Any ideas?

Solomon Rutzky Over a year ago

@JohnD Why would you change the CROSS to OUTER? Off hand it is impossible to say without seeing some data and the logic if the InspectionData TVF.

John D Over a year ago

...I changed to OUTER, because I need the data for each and every vehicle -- and the inspection data may be NULL in some cases. But that is OK, for the report will display default-values when the inspection fields are NULL. thx...John

|

Collectives™ on Stack Overflow

SQL Server user defined function returns table -- cannot call it from select query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related