4

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          #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?

Community
  • 1
  • 1
deusxmach1na
  • 368
  • 6
  • 17

1 Answers1

3

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
Chris Gessler
  • 22,727
  • 7
  • 57
  • 83
  • I was afraid of that. Is there a way I can split my table up into sub-tables by distinct Unit and Line easily? Or will I just have to create a table for each Unit/Line and column I want to pass my UDF? – deusxmach1na May 02 '12 at 16:11
  • @deusxmach1na - are you trying to treat each row as a table? – Chris Gessler May 02 '12 at 16:20
  • Not each row, but all rows with the same Unit and Line should be a table. My UDF will take the Date column and the Revenue column and do a calculation on it which I want it to perform for every distinct Unit and Line value. So in my example table I would want the first two rows as one table since they are Unit D and Line R, and the next two rows as another table since they are Unit H and line W. – deusxmach1na May 02 '12 at 16:23
  • I got a working solution now, thanks! I used ROW_NUMBER() OVER (PARTITION BY Unit, Line ORDER BY Date DESC) to put a 1 in every row where I want to start a new table and iterated through that column to build up my tables. Anyway you answered my original question so thanks! – deusxmach1na May 02 '12 at 16:45
  • One quick note. `DECLARE @t1 TABLE` should be `DECLARE @t1 as dbo.MyTableType` since my UDF expects to get my specific TableType as a parameter. – deusxmach1na May 02 '12 at 17:45