Call function for subset of rows

Question

I have the following data in a SQL Server 2012 database:

CategoryID  Keyword             Type
-------------------------------------
1           open plan           0
1           kitchen             0
2           air conditioned     3
2           spacious            2
2           living room         1 
3           metal               5
3           shingled            4
3           roof                4

This is a simplification of my data.

I have a scalar function which takes this data in as a user defined table type (an entire table of data), does some processing (quite complex, many rules) and then returns a string for each id e.g. for the subset of data with id 1 it will return open plan kitchen, for the subset of data with id 2 it will return air conditioned, spacious living room, and similar for id 3 it will return metal shingled roof. My function must do this for each id. Currently I'm using a cursor to loop through the distinct ids and call the function for each set of data matching the id.

I'm concerned about performance. What is the better approach to do this?

Example of function:

CREATE FUNCTION [dbo].[func_GenerateString]
(
    @MyData InputDataType READONLY
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @outputString VARCHAR(MAX)
    SET @outputString = ''

    -- implement rules
    RETURN @outputString 
END

This is my table type

CREATE TYPE InputDataType AS TABLE 
(   
    CategoryId INT NOT NULL,
    Keyword varchar(100) NULL,
    Type INT NOT NULL,
)
GO

Answer 1

I think you could use workaround like:

SqlFiddleDemo

CREATE TYPE InputDataType AS TABLE 
(   
    CategoryId INT NOT NULL,
    Keyword varchar(100) NULL,
    Type INT NOT NULL
);
GO

CREATE FUNCTION [dbo].[func_GenerateString]( @MyXml XML)
RETURNS VARCHAR(MAX)
AS
BEGIN

    /* Unwrapping */
    DECLARE @MyData AS InputDataType;

    INSERT INTO @MyData(CategoryId, Keyword, Type)
    SELECT 
        [CategoryId] = t.c.value('(CategoryId)[1]', 'int'),
        [Keyword]    = t.c.value('(Keyword)[1]', 'varchar(100)') ,
        [Type]       = t.c.value('(Type)[1]', 'int') 
    FROM @MyXml.nodes('//row') AS t(c);


    DECLARE @outputString VARCHAR(MAX);
    SET @outputString = '';

    -- your logic
    SET @outputString = STUFF(
                       (SELECT ' ' + Keyword
                        FROM @MyData
                        FOR XML PATH('')), 1, 1, '')

    RETURN @outputString 
END;
GO


 /* Main query */
SELECT CategoryId,
    [result] =  [dbo].[func_GenerateString]((SELECT CategoryId, Keyword, Type
                                             FROM tab t1
                                             WHERE t.CategoryId = t1.CategoryId 
                                             FOR XML PATH, ROOT('root')))
FROM tab t
GROUP BY CategoryId;

Instead of TVP parameter I pass XML parameter and immediately inside function I unwrap it back to TVP.