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

Tags:

Sql Server