Pass Parameter in Table Valued Function Using Select Statement

pass parameter in table valued function using select statement

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

Using a select query as parameter in a table-valued function

See if this isn't what you are trying to do.

select fg.id 
from entity e 
cross apply dbo.fn_getrelatedids(e.id) AS fg
WHERE e.name = 'a'

Edit:to add the right select

How to use a table valued function in select clause?

You would use a CROSS APPLY:

SELECT a.rawData, b.*
FROM TableA a
CROSS APPLY FunctionA(a.rawdata) b

Table value function to pass as parameter to generate output inside case statement

Creating a function is well documented so should be your starting point as they have plenty of examples to get you started.

I recommend an Inline Table-Valued Function as they typically perform significantly better than a Multi-Statement Table-Valued Function.

CREATE FUNCTION [dbo].[fnchkID]
(
    @PASSIVENUMBER INT
)
RETURNS TABLE
RETURN
    SELECT RESISTORID, CAPACITORID, VOLT, WATT, PASSIVENUMBER
        , CASE WHEN RESISTORID IN ('100','540') THEN 'A03' ELSE 'A01' END AS NEWRESISTORID
        , CASE WHEN PASSIVENUMBER = 93 THEN
            CASE WHEN CAPACITORID = 'TH' AND VOLT > 0 THEN (VOLT * 1000) 
            WHEN CAPACITORID = 'TH' AND VOLT = 0 THEN (WATT * 1000)
            WHEN CAPACITORID <> 'TH' AND VOLT > 0 THEN VOLT
            ELSE WATT END
        WHEN PASSIVENUMBER = 35 THEN
            CASE WHEN CAPACITORID = 'RN' AND VOLT >0 THEN (VOLT * 10*3/56) 
            WHEN CAPACITORID = 'RN' AND VOLT = 0 THEN (WATT * 100*2/21)
            WHEN CAPACITORID <> 'RN' AND VOLT >10 THEN VOLT
            ELSE WATT END
        END AS POWERTHRESHOLD
    FROM @ELECTRONICS
    WHERE PASSIVENUMBER = @PASSIVENUMBER;

Note: Your calculated column cannot have the same name as your existing column, hence NEWRESISTORID instead of RESISTORID.

How to pass parameters to Table Valued Function

You need to use CROSS APPLY to achieve this

select 
    f.* 
from 
    users u
    cross apply dbo.tvfHello(u.ID) f

executing table valued valued function in select statement

Use CROSS APPLY to execute your function against each row in the table, passing a row's column as the parameter, and joining all the rows from the results in the result.

You didn't give a more concrete example of your query, so I can't give you a clearer example, but this is what you should look for. use CROSS APPLY just like you would an INNER JOIN, only there's no ON clause.

Okay, from the updated query, yours would look something like this:

SELECT Fname, Lname, x.ColumnName
  FROM Employees
       CROSS APPLY mytabfunct(Fname) x

If you just do a SELECT mytabfunct('Joseph') you'll get a result set with some column names. You'll need to change "ColumnName" to one of the column names from your table-valued function in the above statement. You didn't show what the function returns, so I can't get more specific than that. But try this and see if it gets you closer.

use Table valued function return value in select statement

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;

Query to List Table-Valued Functions and their Input Arguments?

INFORMATION_SCHEMA system tables should be avoided, as they are generalized views for non-specific-to-SQL-Server clients. Instead use the sys schema's tables and views.

Unfortunately there is no sys.functions so we have to filter sys.objects instead:

select o.name, p.*, t.name as type_name
from sys.parameters p
join sys.types t on t.user_type_id = p.system_type_id
join sys.objects o on o.object_id = p.object_id
where o.[type] in ('IF', 'TF') -- IF is inline, TF is multi-statement

---

Related Topics