使用 select 语句在表值函数中传递参数 [英] pass parameter in table valued function using select statement

问题描述

我创建了一个表值返回函数，它返回一个表.这是我的函数调用如下

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)

它对我来说工作正常，现在我想在选择语句中使用这个函数调用，所以我可以动态传递 16 ，它基本上是 employeeId .

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

但是现在我如何将 16 作为所有 employeeId 的动态值一一传递.

but now how can i pass 16 as dynamic value of all employeeId one by one .

推荐答案

use 外部/交叉应用:

select *
from Employee as E
    cross apply dbo.[StateFixedTaxesCalculation](3020, E.EmployeeId, 1, 1006) as TC

如果你仍然需要通过TC.EmployeeId = E.EmployeeId过滤，你可以用子查询来做到这一点:

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

这篇关于使用 select 语句在表值函数中传递参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！