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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 User-defined-function question.

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 UDF

if its a scalar valued UDF you can use statement like above

if its table valued you need to use like

select *
from table t
cross apply dbo.fn_StandardDeviation(t.column, @percent)


see below to understand type of UDFs

http://www.sqlteam.com/article/user-defined-functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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...
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2013-01-11 : 10:32:20
thanks!
Go to Top of Page
   

- Advertisement -