At 06:37 AM 9/05/2003 +0700, you wrote:

>Dear mr Arno Brinkman,
>I'm digging up the old archives and I have a follow up question to one
>of your suggestions:
>
> > You can also use the procedure in a sub-select.
> >
> > SELECT
> > ADDRESS.STREET1,
> > ADDRESS.STREET2,
> > ADDRESS.CITY,
> > ADDRESS.STATECD,
> > ADDRESS.ZIPCODE,
> > ADDRESS.OWNERID,
> > PERSON.FIRSTNAME,
> > PERSON.MIDDLENAME,
> > PERSON.LASTNAME,
> > (SELECT FULLNAME FROM FORMATNAME(PERSON.FIRSTNAME,
> > PERSON.MIDDLENAME,
> > PERSON.LASTNAME, 'LNF'))
> > FROM
> > PERSON
> > JOIN ADDRESS ON (PERSON.PERSONID = ADDRESS.OWNERID)
> > WHERE
> > ADDRESS.OWNERID IS NOT NULL
>What is the proper syntax if we want the selectable StoredProc
>FORMATNAME() to return multiple fields? In your example it worked
>because we only need ONE field from the selectable StoredProc.

You cannot return multiple fields from a subquery. Any subquery that
results in more than one row or more than one output column will fail.

When you need multiple columns you should use a join or, if this isn't
practicable, write a selectable stored procedure that returns the output
you want.

helen