User Defined Functions, passing parameters from another udf's results (end result=Crosstab)

Hi All:
I've read a whole slew of posts about creating temp tables using stored
proceedures to get the crosstab ability, but I'm wondering if, for this
specific case, there might be a more efficient way.

What makes this question different from the others that I've read is
that I'm using user defined functions, not tables.  I actually think
that I've got the crosstab thing down, it's just passing the parameter
to the 2nd udf that's messing me up.

I've got a people table and an address table.  Each person can have
multiple addresses.  I need to create a dataset that has in each row
the name of the person, the first address, any second address, and any
third address.  I only need to show the first 3, so if there's 100, I
can just ignore the rest.

I created a user defined function to return the 1st, 2nd, or 3rd
address for a given person.
 udf_ReturnAddress(Pe
rsonID,MatchNumber)

Another user defined function returns the people that I'm looking for
(potential duplicates for a person in this case).
 udf_ReturnPossibleDu
 psForAPerson(PersonI
D)


SELECT
Main.FoundPersonID, Main.LastName, A1.Street, A2.Street,
A3.Street
FROM
 udf(ReturnPossibleDu
 psForAPerson(@Person
ID) MainTable
CROSS JOIN
(SELECT Street1 FROM
 udf_ReturnAddress(Ma
in.FoundPersonID,1) Adr1) A1
CROSS JOIN
(SELECT Street1 FROM
 udf_ReturnAddress(Ma
in.FoundPersonID,2) Adr2) A2
CROSS JOIN
(SELECT Street1 FROM
 udf_ReturnAddress(Ma
in.FoundPersonID,3) Add3) A3


If, for the first parameter for the return address function, I replace
Main.FoundPersonID with the ID of a person, it works just fine.  I
obviously don't want a static id as a parameter - I want to use the ID
of the person that the first udf found.  Leaving the variable
MainTable.PersonID there causes an error in the query designer though.

I get "Error in list of function arguments: '.' not recognized.

So maybe my problem is that I just don't know how to pass the id of the
person that's found by the first UDF as the parameter of the function
to find the found person's 3 addresses.

Any guidance would be greatly appreciated!
Thanks
Ken

I left out that I'm using SQL Server 2000.

Simple stupid mistake!  Never mind.

Had a table UDF, not Scalar UDF for the function that returned the
address number and some messed up thinking.

Here's what I've got now, and it works just fine.

SELECT
Possibles.*,
dbo. udf_ReturnAddress(Fo
undPersonID, 1) AS FullAddr1,
dbo. udf_ReturnAddress(Fo
undPersonID, 2) AS FullAddr2
FROM
dbo. udf_ReturnPossibleDu
 psForAPerson(@Person
ID) Possibles

WAYYYY simpler now.

So, for those of you who may have found this by searching for crosstab,
there's a way to create a crosstab, with no temp table, but with a
FIXED number of columns, not dynamic.  (not that this is a unique
solution, there's lots of other postings on it too...)

Thanks for the reply Erland!

The function can't be optimal the way I've got it, but it works.  It's
not exactly just a plain lookup, it's numbering address rows.  Still,
I'm VERY eager to understand how I could do this with a join for better
performance.  Can you explain?


Here's the funcion
ALTER FUNCTION dbo.udf_ReturnAddress
(
@PersonID int,
@MatchNumber int
)
RETURNS varchar(600)
AS
BEGIN
DECLARE @FullAddr varchar(600)

SELECT  @FullAddr = Addresses.Address1 +
CASE WHEN len(Addresses.Address2) > 0 THEN ', ' + Addresses.Address2
ELSE '' END +
CASE WHEN len(Addresses.Address3) > 0 THEN ', ' + Addresses.Address3
ELSE '' END +
Addresses.City

FROM dbo.ppl_addresses Addresses INNER JOIN
(SELECT COUNT(*) LineNumber, a.PersonID,
a.AddressID
FROM  ppl_Addresses A JOIN
ppl_Addresses B ON A.AddressID >=
B.AddressID AND A.PersonID = B.PersonID
GROUP BY A.PersonID, A.AddressID) N
ON Addresses.PersonID = N.PersonID AND Addresses.AddressID =
N.AddressID

WHERE
(Addresses.PersonID = @PersonID) AND
(N.LineNumber = @MatchNumber)

ORDER BY Addresses.IsMailing DESC, Addresses.IsBilling DESC

RETURN @FullAddr
END

Ken Post (nntp.post@gmail.com)  writes:
> Thanks for the reply Erland!
>
> The function can't be optimal the way I've got it, but it works.  It's
> not exactly just a plain lookup, it's numbering address rows.  Still,
> I'm VERY eager to understand how I could do this with a join for better
> performance.  Can you explain?

Below is an attempt to a rewrite, which may flat out wrong. (I did not
understand the ORDER BY, so I simply ignored those. :-)

Since yours is a bit complicated, it may warrant a function, as long as
performance is decent. But I recently leard that there are people who
do things like:

SELECT OrderId, dbo. GetCustomerName(Cust
omerID), ...
FROM   ...

and the UDF is a plain SELECT. That is very unnecessary.


SELECT Poss.*,
MIN(CASE WHEN N.LineNumber WHEN 1 THEN UDF.FullAddr) AS FullAddr1,
MIN(CASE WHEN N.LineNumber WHEN 2 THEN UDF.FullAddr) AS FullAddr2
dbo. udf_ReturnAddress(Fo
undPersonID, 1) AS FullAddr1,
dbo. udf_ReturnAddress(Fo
undPersonID, 2) AS FullAddr2
FROM  dbo. udf_ReturnPossibleDu
 psForAPerson(@Person
ID) Poss
JOIN  (SELECT Adr.PersonID, N.LineNumber,
FullAddr = Adr.Address1 +
CASE WHEN len(Adr.Address2) > 0
THEN ', ' + Adr.Address2
ELSE ''
END +
CASE WHEN len(Adr.Address3) > 0
THEN ', ' + Adr.Address3
ELSE ''
END + Adr.City
FROM   dbo.ppl_addresses Adr
JOIN   (SELECT COUNT(*) LineNumber, a.PersonID, a.AddressID
FROM   ppl_Addresses A
JOIN   ppl_Addresses B ON A.AddressID >= B.AddressID
AND A.PersonID = B.PersonID
GROUP BY A.PersonID, A.AddressID) N
ON  Adr.PersonID = N.PersonID
AND  Adr.AddressID = N.AddressID
WHERE   N.LineNumber IN (1, 2)) AS UDF
ON  UDF.PersonID = Poss.FoundPersonID
GROUP  BY Poss.PersonID, Poss.col1, Poss.col2, ...



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Ah, now that's interesting.  Totally diffrent way of thinking about it.
While my udf wasn't a plain select, if your rewrite will work, I'd
much rather use that.  I'll test it out tomorrow.  Thanks!

The order by is irrelevant, but in case you're interested, there are 2
bit columns in ppl_Addresses.  One is IsMailing and the other is
IsBilling.  Each person has 1 billing address and 1 mailing address
(though they can't be the same ID).  I was showing first the primary
address, then the billing (if different), then any other addresses on
file.