Call function that returns table in a view in SQL Server 2000

Question

1. SQL Server - Compatibility Level 2000
2. Person table - PersonId, PersonName, etc.. (~1200 records)

Two user functions - GetPersonAddress(@PersonId), GetPaymentAddress(@PersonId)

These two functions return data in a table with Street, City etc...(one record in the return table for the PersonId)

I have to create a view that joins the person table with these two user functions by passing in the person id.

Limitations:

• Cross Apply is not supported on a function in SQL Server 2000
• Cursor, temp table and temp variables are not supported in views so that I can loop upon the person table and call the functions.

Can someone help?

Answer 1

You could create functions GetPeopleAddresses() and GetPaymentsAddresses() which return PersonId as a field and then you can use them in JOIN:

SELECT t.PersonId, PersonName, etc..., a1.Address, a2.Address
FROM YourTable t 
  LEFT JOIN GetPeopleAddresses() a1 ON a1.PersonId = t.PersonId
  LEFT JOIN GetPaymentsAddresses() a2 ON a2.PersonId = t.PersonId

Of course, your functions have to return only unique records