Call table value function inside my query in sql server

Question

I create this function

ALTER FUNCTION [dbo].[FxPackageReport]
( @packageId int )
RETURNS table
AS
RETURN (
        select *from TestPackageReportDetails where TestPackageId=@packageId and step='LineCheck'
         )
select *from dbo.fxpackagereport(5457)

The result :

I want to merge this columns inside my select something like this :

select id,select *from dbo.fxpackagereport(id), from testpackage

Is there any solution to merge this result with my query >?I just want to bring the table result inside my query .

Possible duplicate of [SQL Join table-valued function with table where table field is a function input](http://stackoverflow.com/questions/4764994/sql-join-table-valued-function-with-table-where-table-field-is-a-function-input) — AHiggins, Aug 31 '16 at 15:40

score 2 · Accepted Answer · answered Aug 31 '16 at 15:39

2

The UDF seems rather straight forward so rather than using the UDF, a simple join may due.

   Select A.*
          ,B.* 
    From testpackage A
    Join TestPackageReportDetails B
      on (TestPackageId=A.ID and step='LineCheck')

Another option would be to use a CROSS APPLY and your UDF

select A.id
      ,B.*
from testpackage A
Cross Apply (Select * from dbo.fxpackagereport(A.id)) B

answered Aug 31 '16 at 15:39

John Cappelletti

79,615
7
44
66

In fact the problem is a little complex ,in this question :http://stackoverflow.com/questions/39243771/efficiently-convert-rows-to-columns-in-sql-server-view i get my solution but i want to change this solution to storeprocedure – Ehsan Akbar Aug 31 '16 at 15:42
I just want to add my row to column – Ehsan Akbar Aug 31 '16 at 15:43
It works ,I just add multi cross apply .thank you :) – Ehsan Akbar Aug 31 '16 at 15:53
Just one more question how can i use cross apply like left join ? – Ehsan Akbar Aug 31 '16 at 15:55
select A.id ,B.* ,C.* from TestPackages A Cross Apply (Select * from dbo.fxpackagereport(A.id,'LineCheck')) B Cross Apply (Select * from dbo.fxpackagereport(A.id,'Clean')) C – Ehsan Akbar Aug 31 '16 at 15:55
Because here some values that don't exist in fxpackagereport table ,but it should be in my output – Ehsan Akbar Aug 31 '16 at 15:56

Call table value function inside my query in sql server

1 Answers1