[SOLVED] T-SQL call table function passing multiple values and aggregate results

T-SQL call table function passing multiple values and aggregate results

I have a Table function which returns a set of rows based on a parameter, which looks like this example:

CREATE FUNCTION fn_get_records
(@PARENTID INT)
RETURNS  @returnTable TABLE 
(
    -- columns returned by the function
    Id INT NOT NULL,
    Parent_Id INT NOT NULL,
    Name VARCHAR(255) NOT NULL
)
AS
BEGIN
   -- This select returns data
   INSERT INTO @returnTable (Id, ParentId, Name)
   SELECT Id, ParentId, Name FROM [whatever] where Parent_Id = @PARENTID 
RETURN
END

I have another table which contains a list of those "Parent Ids" and I should call the previous function for each Parent_Id that matches a specific query and aggregate all results.

The parent Ids are retrieved using something like

SELECT Parent_Id 
FROM Parent_Records
WHERE Country = 'USA'

This select returns, for example, 4 rows. For each row I have to execute my function fn_get_records and aggregate all results together in a view or in another function.

Is it even possible? Of course I don't want to use cursors because I need something fast

Solution

Just a quick FYI: A more efficient TVF would be a single statement

CREATE FUNCTION fn_get_records (@PARENTID INT)
RETURNS TABLE 
AS
RETURN (
   SELECT Id, ParentId, Name FROM [whatever] where Parent_Id = @PARENTID 
)
END

Then you can call your function via a CROSS Apply. For Example:

Select A.*
      ,B.*
 From  YourTable A
 Cross Apply dbo.fn_get_records (A.SomeIntValue) B