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

[Ken Post]

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(PersonID,MatchNumber)

Another user defined function returns the people that I'm looking for
(potential duplicates for a person in this case).
udf_ReturnPossibleDupsForAPerson(PersonID)
SELECT
Main.FoundPersonID, Main.LastName, A1.Street, A2.Street,
A3.Street
FROM
udf(ReturnPossibleDupsForAPerson(@PersonID) MainTable
CROSS JOIN
(SELECT Street1 FROM
udf_ReturnAddress(Main.FoundPersonID,1) Adr1) A1
CROSS JOIN
(SELECT Street1 FROM
udf_ReturnAddress(Main.FoundPersonID,2) Adr2) A2
CROSS JOIN
(SELECT Street1 FROM
udf_ReturnAddress(Main.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

[Ken Post]

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

[Ken Post]

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(FoundPersonID, 1) AS FullAddr1,
dbo.udf_ReturnAddress(FoundPersonID, 2) AS FullAddr2
FROM
dbo.udf_ReturnPossibleDupsForAPerson(@PersonID) 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...)

[Erland Sommarskog]

Ken Post (nn*******@gmail.com) writes:

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(FoundPersonID, 1) AS FullAddr1,
dbo.udf_ReturnAddress(FoundPersonID, 2) AS FullAddr2
FROM
dbo.udf_ReturnPossibleDupsForAPerson(@PersonID) Possibles

WAYYYY simpler now.

OK, but what is this udf_ReturnAddress actually doing? Is just doing a
plain lookup with a SELECT statement, like:

BEGIN
RETURN (SELECT FullAddress FROM addresses
WHERE PersonId = @personid AND Adrno = @no)
END

In such case, rewrite into a join for a considerable performance
improvement.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Ken Post]

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

[Erland Sommarskog]

Ken Post (nn*******@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(CustomerID), ...
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(FoundPersonID, 1) AS FullAddr1,
dbo.udf_ReturnAddress(FoundPersonID, 2) AS FullAddr2
FROM dbo.udf_ReturnPossibleDupsForAPerson(@PersonID) 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, es****@sommarskog.se

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

[Ken Post]

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.