SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 User-defined-function question.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kotonikak
Yak Posting Veteran

81 Posts

Posted - 01/10/2013 :  14:47:34  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 01/10/2013 :  22:37:41  Show Profile  Reply with Quote
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

81 Posts

Posted - 01/11/2013 :  10:10:23  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 01/11/2013 :  10:18:32  Show Profile  Reply with Quote
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

81 Posts

Posted - 01/11/2013 :  10:32:20  Show Profile  Reply with Quote
thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000