CODEWITHSUNDEEP
sqlsql-server
Rajakrishnan
Rajakrishnan

Reputation: 161

use Table valued function return value in select statement

I Have a table-valued function which return 2 columns and 1 row. I would like to use those values in the select statement. Kindly help me in fetching the value from table.

Edit (From OP's comments)

something like 

select a,b (select c1 from MyTableFunction(Param1, Param2)) as c, 
           (select c2 from MyTableFunction(Param1, Param2)) as d 
from main_table

Upvotes: 2

Views: 5763

Answers (2)

StuartLC
StuartLC

Reputation: 107407

If its Sql Server, this is as simple as:

SELECT Col1, Col2 
FROM Schema.MyTableFunction(Param1, Param2);

Edit

If the parameters passed to the Function are constants / independent of the other joined tables, you can use a CROSS JOIN to it, alias it and use the result columns.

SELECT a,b, X.Col1 as c, X.Col2 as d 
FROM main_table mt
 CROSS JOIN MyTableFunction(123, 'SomeConstant') AS X;

However, if you need to pass in columns from a table into a table valued function during a join, you need to use CROSS APPLY

SELECT a,b, X.Col1 as c, X.Col2 as d 
FROM main_table mt
 CROSS APPLY MyTableFunction(mt.Col1, mt.Col2) AS X;

Upvotes: 2

Patrick Hofman
Patrick Hofman

Reputation: 157146

In SQL Server just put the function name in the from. So:

select column1
,      column2
from   dbo.udFunctionName('xyz')

In Oracle use:

select column1
,      column2
from   table(functionName('xyz'))

Upvotes: 0

Related Questions