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_ReturnAddre ss(PersonID,Mat chNumber)

Another user defined function returns the people that I'm looking for
(potential duplicates for a person in this case).
udf_ReturnPossi bleDupsForAPers on(PersonID)


SELECT
Main.FoundPerso nID, Main.LastName, A1.Street, A2.Street,
A3.Street
FROM
udf(ReturnPossi bleDupsForAPers on(@PersonID) MainTable
CROSS JOIN
(SELECT Street1 FROM
udf_ReturnAddre ss(Main.FoundPe rsonID,1) Adr1) A1
CROSS JOIN
(SELECT Street1 FROM
udf_ReturnAddre ss(Main.FoundPe rsonID,2) Adr2) A2
CROSS JOIN
(SELECT Street1 FROM
udf_ReturnAddre ss(Main.FoundPe rsonID,3) Add3) A3


If, for the first parameter for the return address function, I replace
Main.FoundPerso nID 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.Perso nID 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]

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

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

[Ken Post]

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

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_ReturnA ddress(FoundPer sonID, 1) AS FullAddr1,
dbo.udf_ReturnA ddress(FoundPer sonID, 2) AS FullAddr2
FROM
dbo.udf_ReturnP ossibleDupsForA Person(@PersonI D) 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]

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

Ken Post (nntp.post@gmai l.com) writes:[color=blue]
> 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_ReturnA ddress(FoundPer sonID, 1) AS FullAddr1,
> dbo.udf_ReturnA ddress(FoundPer sonID, 2) AS FullAddr2
> FROM
> dbo.udf_ReturnP ossibleDupsForA Person(@PersonI D) Possibles
>
> WAYYYY simpler now.[/color]

OK, but what is this udf_ReturnAddre ss 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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ken Post]

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_ReturnA ddress
(
@PersonID int,
@MatchNumber int
)
RETURNS varchar(600)
AS
BEGIN
DECLARE @FullAddr varchar(600)

SELECT @FullAddr = Addresses.Addre ss1 +
CASE WHEN len(Addresses.A ddress2) > 0 THEN ', ' + Addresses.Addre ss2
ELSE '' END +
CASE WHEN len(Addresses.A ddress3) > 0 THEN ', ' + Addresses.Addre ss3
ELSE '' END +
Addresses.City

FROM dbo.ppl_address es 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.Perso nID = N.PersonID AND Addresses.Addre ssID =
N.AddressID

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

ORDER BY Addresses.IsMai ling DESC, Addresses.IsBil ling DESC

RETURN @FullAddr
END

[Erland Sommarskog]

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

Ken Post (nntp.post@gmai l.com) writes:[color=blue]
> 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?[/color]

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.GetCustomer Name(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_ReturnA ddress(FoundPer sonID, 1) AS FullAddr1,
dbo.udf_ReturnA ddress(FoundPer sonID, 2) AS FullAddr2
FROM dbo.udf_ReturnP ossibleDupsForA Person(@PersonI D) Poss
JOIN (SELECT Adr.PersonID, N.LineNumber,
FullAddr = Adr.Address1 +
CASE WHEN len(Adr.Address 2) > 0
THEN ', ' + Adr.Address2
ELSE ''
END +
CASE WHEN len(Adr.Address 3) > 0
THEN ', ' + Adr.Address3
ELSE ''
END + Adr.City
FROM dbo.ppl_address es 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.FoundPerso nID
GROUP BY Poss.PersonID, Poss.col1, Poss.col2, ...



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ken Post]

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

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.