Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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) 

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 .

like image 516
rahularyansharma Avatar asked Oct 24 '13 08:10

rahularyansharma


People also ask

How do you pass a table as a parameter in SQL?

Create a user-defined table type that corresponds to the table that you want to populate. Pass the user-defined table to the stored procedure as a parameter. Inside the stored procedure, select the data from the passed parameter and insert it into the table that you want to populate.

Can we pass table as parameter in function?

You can, however no any table. From documentation: For Transact-SQL functions, all data types, including CLR user-defined types and user-defined table types, are allowed except the timestamp data type.


1 Answers

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 
like image 199
Roman Pekar Avatar answered Oct 03 '22 10:10

Roman Pekar