pass parameter in table valued function using select statement

Question

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 .

roman · Accepted Answer · 2015-03-05 11:05:42Z

67

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

edited Mar 5, 2015 at 11:05

answered Oct 24, 2013 at 8:15

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rahularyansharma Over a year ago

Thanks its works for me . I have some few more difficulties but i post them as a separate question .

Milind Thakkar Over a year ago

After searching for hours (mostly on wrong keywords may be), I came to this question. It saved my day. Thanks

jediCouncilor Over a year ago

Great job on this one, I don't get to use cross apply too often, so I always have to go look it up :)

Collectives™ on Stack Overflow

pass parameter in table valued function using select statement

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related