Create a user defined function

Hi

I need to create a user defined function that i can embed into SQL-statements, that returns a string. the objective is to get a 'flat list' of all employees that are associated to a certain order. I never wrote a SP or user defined function so i can only show you the pieces of code that i need to put together:

input param:
@param_ID_ORDER number(11)

sql:
SELECT DISTINCT P.EMPLOYEE EMP FROM P_ORDER P, S_DATA S WHERE P.ID=S.P_ID AND S.O_ID = param_ID_ORDER ORDER BY P.EMPLOYEE; Then i want to concatenate all users and return the string

Pseudocode (C#-Syntax)

string s = "";
while (!rs.EOF)
{
  s += rs!EMP + ";"
  rs.MoveNext();
}

return s;

My Questions:
How do i define the return value as String? How do i get te cursor/recordset?
What is the syntax for the WHILE-Loop?
How do i declare the string so it takes at least 1024 chars? How do I check the string-maxlen isn't exceeded (something like: If Len(myString) > MAXLEN)?
Can I set optional parameters, e.g. instead or in addition to the param_ID_ORDER two dates, specifying a daterange? How do I grant select/excution privilges for a certain ROLE defined in my schema to use this UDF in select statements?

Any help very much appreciated,
Alex Received on Fri Mar 10 2006 - 03:03:25 CST