Joining a table value function to a MSSQL query
In SQL Server 2005 you can use the CROSS APPLY syntax:
select id_num, name, balance, SUM(x.discount)
from listOfPeople
cross apply dbo.calculatePersonalDiscount(listOfPeople.id_num) x
Likewise there's an OUTER APPLY syntax for the equivalent of a LEFT OUTER join.
I needed this badly and you gave me the start, and here I was able to JOIN on another View. (another table would work too), and I included another function in the WHERE clause.
SELECT CL_ID, CL_LastName, x.USE_Inits
FROM [dbo].[tblClient]
JOIN dbo.vw_InsuranceAppAndProviders ON dbo.vw_InsuranceAppAndProviders.IAS_CL_ID = tblClient.CL_ID
CROSS APPLY dbo.ufx_HELPER_Client_CaseWorker(tblClient.CL_ID) x
WHERE dbo.vw_InsuranceAppAndProviders.IAS_CL_ID = tblClient.CL_ID
AND dbo.ufx_Client_IsActive_By_CLID(tblClient.CL_ID) = 1
AND (dbo.vw_InsuranceAppAndProviders.IAS_InsuranceType = 'Medicare')