Microsoft SQL Server forum - User Defined Functions, passing parameters from another udf's results (end result=Crosstab)

Author

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

Ken Post

2005-10-27, 9:25 am

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

Ken Post

2005-10-27, 9:25 am

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

Ken Post

2005-10-27, 9:25 am

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...)

Ken Post

2005-10-27, 9:25 am

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

2005-10-27, 9:25 am

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

Ken Post

2005-10-27, 9:25 am

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.

Sponsored Links