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

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

.

Relevant Pages

  • Re: Can lines collection be used to assist me in automatically numbering lines?
    ... It's just that most coders find other techniques faster and at least ... >If you like using line numbers and would like something to do the numbering ... check out the excellent freeware at: ... Prev by Date: ...
    (comp.databases.ms-access)
  • Re: Week number to date??
    ... or rather how the week numbering start is defined. ... > I'm using this to compare year to year....using the week number as the ... > common factor. ... Prev by Date: ...
    (comp.databases.ms-access)
  • Page X of Y, where Y is total # of pages in section...
    ... We've found we can restart the numbering for subsequent sections, ... Raymond ... Prev by Date: ...
    (microsoft.public.word.docmanagement)
  • Re: next Narnia film?
    ... regardless of the messed up order that publishers are numbering them ... Prev by Date: ...
    (rec.arts.movies.current-films)
  • Re: Help
    ... I agree with you that my section numbering is ... not typical, but the way the manuscript has been designed, it makes ... I will appreciate if you could elaborate on both these solutions. ... Prev by Date: ...
    (comp.text.tex)