# User Defined Function - Passing and Processing a Table

• Posted this issue up earlier... but no responses so thought would reword. Here's the problem:

I am pulling some data out using pretty complex inner joins - and outputting the data in a pivot table. To put data out, each element in the pivot table needs to be created by an aggregate function (sum, max, min, avg etc.). I am trying to write my own aggregate function - something which functions like a weigthed average. It would function something like this:

Input: two columns of data, each element in the first corresponding to the coresponding one in the second column (one to one correspondence)… for example the first column is the salary of 10 people and the second is their age… something like:

SalaryAge

1000025

1500027

1200018

1500036

1600057

1700044

1800032

1900056

2500034

750029

Output: Will be a weighted average of the salaries - weights being the ages. So something on the lines of

X = (10000*25 + 15000* 27 + 12000*18 + 15000*36… +7500*29)/(25 + 27 + 18 … +29)

Structure: So essentially I would need to pass two columns (tables) to the function - and the function would return a float which would have the answer.

This is where all my SQL knowledge fails. I have absolutely no clue how to do this… An alternative I thought is if I can write a function to convert the column to a comma separated list - but again, passing columns to a user defined function in SQL is something which I don’t recall from anywhere!

• select sum(salary*age)/sum(age)

• It seems inelegant, but create a #temp table (with an agreed upon name) to hold the two argument columns in the calling query, and let the called function/procedure reference that #temp table to get its input values. It may not be as inefficient as you might fear, since most of the work will probably be done in main memory anyway -- you never know till you try it and see.

I too am disappointed that the SQL language does not play nicer with arrays as objects.

• If you are wanting to pass a table to a function you might want to consider converting the table to XML and passing the XML as the argument to the function. Perhaps something along the line of:

returns table

as

return

( select

t.value('./@Salary', 'integer') as Salary,

t.value('./@Age', 'integer') as Age

from @xmlMatrix.nodes('//row') x(t)

)

go

declare @source table

( Salary integer,

age tinyint

)

insert into @source

select 10000, 25 union all

select 15000, 27 union all

select 12000, 18 union all

select 15000, 36 union all

select 16000, 57 union all

select 17000, 44 union all

select 18000, 32 union all

select 19000, 56 union all

select 25000, 34 union all

select 7500, 29

--select * from @source

declare @functionArgument xml

select @functionArgument =

( select

Salary as [row/@Salary],

Age as [row/@Age]

from @source

for xml path('')

)

--select @functionArgument as [@functionArgument]

/* -------- Sample Output: --------

Salary Age

----------- -----------

10000 25

15000 27

12000 18

15000 36

16000 57

17000 44

18000 32

19000 56

25000 34

7500 29

*/

• I've been coding SQL in Sybase and Oracle more recently, but if you are using SQL Server 2000 or 2005, I believe you could use table variables to pass your 2 tables (why not combine into 1 table variable that has 2 columns). Table variables are similar to temp tables in some ways and similar to arrays in another. I suggest searching on this site, Online Books, or MSDN for more information about table variables.

Good luck.

Thanks,
Greg

• Table variables are not available to be used as arguments to procedures or functions in either SQL 2000 or SQL 2005. Table variables are a feature that continues to plan an increasing role in SQL Server and I believe that in version 2008 you will be able to do more than present as far as passing matrices or tables; however, in the current production versions of SQL server this feature is not yet avaiable. I prefer passing something like a pipe delimited list if the argument is a simple list -- that is a table composed of only 1 column; however, once multiple columns come into play I begin to prefer XML as the method of representing a table as an argument to either a function or a procedure.

• I need to use this function as a part of a pivot table - so it has to be an aggregation kind of function.

Because I am using the structure of the pivot table query to do my dirty work, I am unable to write into temp tables and all.

The XML idea sounds more usable - will try that...

Even if someone can help with a function to convert a table column into a comma separated list, it will be more than enough - I have the stuff after that figured out!

Cheers

PuneetSingh

• I am confused. Why isn't robert's answer sufficient?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc.
[/font]
[font="Verdana"] "Performance is our middle name."[/font]

• Perhaps if you could paste in the 'dirty work' too people might be able to help more. I get the impression that its hard because we only have half the problem with restrictions that might not need to be there if we knew the whole picture.

• If you would rather have two distinct comma delimited lists this is also an easy task to perform using XML formatting. That can be done using the same source data as before with something like:

declare @list1 varchar(60)

declare @list2 varchar(30)

set @list1 = substring(

( select ',' + cast(Salary as varchar(11)) as [text()]

from @source

for xml path('')), 2, 199)

set @list2 = substring(

( select ',' + cast(Age as varchar(3)) as [text()]

from @source

for xml path('')), 2, 199)

select @list1 as [@list1]

select @list2 as [@list2]

/* -------- Sample Output: --------

@list1

------------------------------------------------------------

10000,15000,12000,15000,16000,17000,18000,19000,25000,7500

@list2

------------------------------

25,27,18,36,57,44,32,56,34,29

*/

• aggregate functions need to be coded as CLR's. Look into VS if you really need an aggregate.

You can get pretty complex and my experience is they don't perform as well as a direct SQL statement. I did some tests once and even going RBAR was faster than calling the CLR.

• rbarryyoung (4/16/2008)

I am confused. Why isn't robert's answer sufficient?

I agree... why is everyone still looking for an aggegate function and passing columns of data when Robert's very simple formula appears to do the trick???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• Jeff Moden (4/25/2008)

rbarryyoung (4/16/2008)

I am confused. Why isn't robert's answer sufficient?

I agree... why is everyone still looking for an aggegate function and passing columns of data when Robert's very simple formula appears to do the trick???

per the OP

puneetsingh77 (4/16/2008)

I need to use this function as a part of a pivot table - so it has to be an aggregation kind of function.

Because I am using the structure of the pivot table query to do my dirty work, I am unable to write into temp tables and all.

The XML idea sounds more usable - will try that...

Even if someone can help with a function to convert a table column into a comma separated list, it will be more than enough - I have the stuff after that figured out!

Cheers

PuneetSingh

• So use Robert's function as part of a derived table, join it, and pivot it.

It really would help if the OP would post exactly what output is expected from the input in the original post, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".