User Defined Function - Passing and Processing a Table

Question

Post reply

User Defined Function - Passing and Processing a Table

puneetsingh77

SSC Enthusiast

Points: 135
More actions
April 15, 2008 at 12:23 am

#74888

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!
Help please!!!

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 1

Robert-378556

SSCertifiable

Points: 5917

April 15, 2008 at 4:53 am

#802439

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

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 2

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.

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 3

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:

create function dbo.ReadXml (@xmlMatrix xml)

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]

select * from readXml(@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

*/

gmakepeace SSC Rookie Points: 42 More actions · Answer 4

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

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 5

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.

puneetsingh77 SSC Enthusiast Points: 135 More actions · Answer 6

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

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]

Ken Gaul Default port Points: 1422 More actions · Answer 8

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.

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 9

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

*/

Bob Fazio SSChampion Points: 10794 More actions · Answer 10

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.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 11

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bob Fazio SSChampion Points: 10794 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 13

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 14

We have the same problem in the MSDN SQL Server forums; like this timeI I frequently "give up" on the post when I really don't know what the objective is.