Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
kotonikak
Yak Posting Veteran
92 Posts |
Posted - 2013-01-10 : 14:47:34
|
Hello. I was wondering if it is possible to insert a column as a parameter in a user-defined function and have it return a value that you calculate within the function?Say I have a column in a table with multiple values. I want to insert this column into a UDF and then use this function in a select statement to give me one value (say the standard deviation of that column).so:select dbo.fn_StandardDeviation(column, @percent)from table(dbo.fn_StandardDeviation is my function)I want my function to have two parameters, the column (say it has 10 rows), and a parameter @percent float.Is this possible?Do I have to use a user-defined aggregate function instead? (i've heard of it but don't know how to use it).Any help will be greatly appreciated! |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-10 : 22:37:41
|
depends on return type of UDFif its a scalar valued UDF you can use statement like aboveif its table valued you need to use like select *from table tcross apply dbo.fn_StandardDeviation(t.column, @percent)see below to understand type of UDFshttp://www.sqlteam.com/article/user-defined-functions------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
kotonikak
Yak Posting Veteran
92 Posts |
Posted - 2013-01-11 : 10:10:23
|
I understand the types and it seems to me that the function is calculating whatever math function one row at a time.What I'm trying to do is pass in an array (if that's even possible) and having it return a numeric value (one row, one column).For example:I want to pass in a column that has 10 rows (say numbers 1-10) and I want my function to calculate the minimum of that column.When I call my function with a select statement, it returns one row, one column with the value 1 (minimum of 1-10).Just wondering if this is possible... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 10:18:32
|
nope..not possible as it involves dynamic sql. You can do it in a procedure though------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
kotonikak
Yak Posting Veteran
92 Posts |
Posted - 2013-01-11 : 10:32:20
|
thanks! |
|
|
|
|
|