Posted by Ken Post on 10/25/05 20:21

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

[Back to original message]