SQL - 在Select中将表传递给UDF

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:

我有一个UDF，它将一个表作为参数（一个2列表）并输出一个字符串，就像这篇文章一样。我有一个表，我想将UDF应用于多个列，如下所示：

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:

我想在具有最新日期的该表的每一行上调用我的UDF，并向UDF传递一个表，其中包含每个不同单元和行的Date和Revenue列。我还想调用UDF并为每个不同的Unit和Line传递Date和Orders。这是一个报告，UDF将始终传递Date列和我想要应用某些计算并存储和报告的另一列。所以我看了很多东西，包括CROSS APPLY，我想做这样的事情：

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          #Table T
WHERE         T.Date = @ReportDate

This gives me the error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." I looked at CROSS APPLY but that seems only apply for when you are returning a table variable. So I could probably answer my own question by using a cursor and a loop maybe and constructing each table I want to pass to the UDF in each loop iteration but that just doesn't seem right. Can anyone give me an idea how to do what I want in SQL Server 2008?

这给了我错误“当EXISTS没有引入子查询时，只能在选择列表中指定一个表达式。”我查看了CROSS APPLY，但这似乎只适用于返回表变量的情况。因此，我可以通过使用游标和循环来回答我自己的问题，并构建我想在每次循环迭代中传递给UDF的每个表，但这似乎不对。谁能让我知道如何在SQL Server 2008中做我想做的事情？

1 个解决方案

#1

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:

不能像这样使用子查询来返回查询的SELECT部分中的表变量。他们只能返回一个值。如：

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

要么

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

#1