T-sql – capture output of table-valued function

t-sql

Is it possible to capture output of table-valued function? For example, function is defined as follows.

CREATE FUNCTION [dbo].[ufn_MyFunction]
(   
    @ItemId INT
)
RETURNS @ReturnTable TABLE 
(
    RowId int primary key NOT NULL,
    Value nvarchar(250) NOT NULL
)
AS
BEGIN
    -- ...
    RETURN;
END

I understand you can do SELECT * FROM ufn_MyFunction(@id), but I need to use values from every row of the returned table. Is there a way to achieve this?

Best Answer

Sure you can, you just define a table variable or a temp table and pipe the output from the select into that. Then you can use that in your code.

So, something like this;

--Declare some working variables
    declare @t Table ( rowId INT, Value nvarchar(250))
    declare @id int

--Gather the output from the function
    insert into @t
    SELECT * FROM ufn_MyFunction(@id)

-- Use the values
    select *
    from @t
Related Question