I have created a table valued return function which returns me a table . here is call of my function as follow
SELECT * FROM dbo.[StateFixedTaxesCalculation](3020,16,1,1006)
and its working OK for me , now i want to use this function call in a select statment , so i can pass 16
which is basically employeeId
dynamically.
So i have decided to use inner join with table returned by that function . Like this
SELECT * FROM Employee as E
INNER JOIN dbo.[StateFixedTaxesCalculation](3020,16,1,1006) as TC ON TC.EmployeeId=E.EmployeeId
but now how can i pass 16
as dynamic value of all employeeId
one by one .
use outer/cross apply
:
select *
from Employee as E
cross apply dbo.[StateFixedTaxesCalculation](3020, E.EmployeeId, 1, 1006) as TC
if you still have to filter by TC.EmployeeId = E.EmployeeId
, you can do this with subquery:
select *
from Employee as E
cross apply (
select TT.*
from dbo.[StateFixedTaxesCalculation](3020, E.EmployeeId, 1, 1006) as TT
where TT.EmployeeId = E.EmployeeId
) as TC