SQL - Pass Table to a UDF in a Select

Question

I have a UDF that takes a table as a parameter (a 2 column table) and outputs a string, much like this article. I have a table that I want to apply the UDF over multiple columns like this: ``` Date Unit Line Revenue Orders 4/1/12 D R 20.00 3 4/2/12 D R 25.00 4 4/1/12 H W 33.00 1 4/2/12 H W 35.00 3 ``` I want to call my UDF on every row of this table that has the most current date and pass the UDF a table with the columns Date and Revenue for each distinct Unit and Line. And I also want to call the UDF and pass Date and Orders for each distinct Unit and Line. This is a report and the UDF will always get passed the Date column and another column that I want to apply some calculation to and store and report. So I have looked at a lot of things including CROSS APPLY and I want to do something like this: ``` SELECT T.unit , T.line , dbo.fn_myUDF((SELECT T2.Date, T2.Revenue FROM #Table T2)) as UDFRevenueResult , dbo.fn_myUDF((SELECT T2.Date, T2.Orders FROM #Table T2)) as UDFOrderResult FROM…

Accepted Answer

Subqueries can't be used like that to return table variables in the SELECT portion of the query. They can only return a single value. As in:

```
set @myParam = (select myvalue from table where primarykey = 1)
```

or

```
set @myParam = (select top 1 myvalue from table)
```

But, considering that your subqueries do not have any relation to the rows being selected, you can do something like this.

```
declare @t1 table (DateTime c1, float c2)
insert @t1
SELECT Date, Revenue 
FROM #Table

declare @t2 table (DateTime c1, int c2) -- not sure on data type of 'Orders'
insert @t2
SELECT Date, Orders FROM #Table

SELECT T.unit               
     , T.line               
     , dbo.fn_myUDF(@t1) as UDFRevenueResult               
     , dbo.fn_myUDF(@t2) as UDFOrderResult 
FROM #Table T 
WHERE T.Date = @ReportDate 
```

SQL - Pass Table to a UDF in a Select

Solution

Problem

Related problems