How to Execute Table Valued Function

How to execute Table valued function

A TVF (table-valued function) is supposed to be SELECTed FROM. Try this:

select * from FN('myFunc')

How to execute table valued function in SQL Server

You can't pass whole table,like the way,you are trying now..you can use cross apply to get all

you can try below

select * from dbo.LINK  r
cross apply
dbo. fn_security(r.rk)

Execute table valued function from row values

I believe that this should do what you need, using dynamic SQL to generate a single statement that can give you your results and then using that with EXEC to put them into your table. The FOR XML trick is a common one for concatenating VARCHAR values together from multiple rows. It has to be written with the AS [text()] for it to work.

--=========================================================
-- Set up
--=========================================================
CREATE TABLE dbo.TestTableFunctions (function_name VARCHAR(50) NOT NULL, parameter VARCHAR(20) NOT NULL)
INSERT INTO dbo.TestTableFunctions (function_name, parameter)
VALUES ('fn_one', '1001'), ('fn_two', '1001'), ('fn_one', '1002'), ('fn_two', '1002')

CREATE TABLE dbo.TestTableFunctionsResults (function_name VARCHAR(50) NOT NULL, parameter VARCHAR(20) NOT NULL, result VARCHAR(200) NOT NULL)
GO
CREATE FUNCTION dbo.fn_one
(
    @parameter VARCHAR(20)
)
RETURNS TABLE
AS
RETURN
    SELECT 'fn_one_' + @parameter AS result
GO
CREATE FUNCTION dbo.fn_two
(
    @parameter VARCHAR(20)
)
RETURNS TABLE
AS
RETURN
    SELECT 'fn_two_' + @parameter AS result
GO

--=========================================================
-- The important stuff
--=========================================================

DECLARE @sql VARCHAR(MAX)

SELECT @sql = 
(
    SELECT 'SELECT ''' + T1.function_name + ''', ''' + T1.parameter + ''', F.result FROM ' + T1.function_name + '(' + T1.parameter + ') F UNION ALL ' AS [text()]
    FROM
        TestTableFunctions T1
    FOR XML PATH ('')
)

SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 10)

INSERT INTO dbo.TestTableFunctionsResults
EXEC(@sql)

SELECT * FROM dbo.TestTableFunctionsResults

--=========================================================
-- Clean up
--=========================================================
DROP TABLE dbo.TestTableFunctions
DROP TABLE dbo.TestTableFunctionsResults
DROP FUNCTION dbo.fn_one
DROP FUNCTION dbo.fn_two
GO

The first SELECT statement (ignoring the setup) builds a string which has the syntax to run all of the functions in your table, returning the results all UNIONed together. That makes it possible to run the string with EXEC, which means that you can then INSERT those results into your table.

A couple of quick notes though... First, the functions must all return identical result set structures - the same number of columns with the same data types (technically, they might be able to be different data types if SQL Server can always do implicit conversions on them, but it's really not worth the risk). Second, if someone were able to update your functions table they could use SQL injection to wreak havoc on your system. You'll need that to be tightly controlled and I wouldn't let users just enter in function names, etc.

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 a table-valued function in a SELECT column list?

You can cross apply a TVF to a regular table e.g.

SELECT
 x.a
 , x.b
 , x.d
 , y.*
FROM x
cross apply FN(x.c) y;

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

Inline table-valued function without a parameter

Consider using DATETIMEOFFSET and AT TIMEZONE instead of DATEADD. This will properly convert times across time zones and apply the appropriate time change rules. You can convert the DATETIMEOFFSET result to another type (datetime2 in this example) if needed.

I don't think your intent is to include the parenthesis in the function name so I removed it in this example.

CREATE FUNCTION [dbo].[Azure_EST_GetDate] (
)

RETURNS TABLE 
AS
RETURN 
(
    SELECT CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS datetime2(3)) AS handate
);
GO

EDIT

A table-valued function can generally be used in queries wherever a table may be specified. For example:

SELECT handate FROM dbo.Azure_EST_GetDate();

Table-valued functions are appropriate when more than one column or row is returned. You could alternatively use a scalar function for your need since a scalar (single) value is returned. Below is the equivalent scalar function.

CREATE FUNCTION [dbo].[Azure_EST_GetDate_Scalar] ()
RETURNS datetime2(3)
AS
BEGIN
    RETURN CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS datetime2(3));
END;
GO

This scalar function can be used instead of GETDATE() in Azure SQL Database to return a local time:

SELECT dbo.Azure_EST_GetDate_Scalar() AS handate;

SQL Server: How to call table valued function without providing input parameter values

Try this:

CREATE FUNCTION fx_my_table_valued_function
(@my_input_parameter AS VARCHAR(30) = 1)
RETURNS @tbl TABLE (my_column VARCHAR(max))
AS
BEGIN
RETURN 
END

Then:

SELECT * FROM fx_my_table_valued_function(default)

SQL Fiddle