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

Looks like http://sqlfiddle.com/#!6/e5424/1/0 Probably using CASE, functions and so you don't need cursor at all — Lukasz Szozda, Sep 26 '15 at 19:12
Thanks, but that's not calling my function. This is a simplification of a complex scenario, there are other rules involved so my function must be called — newbie_86, Sep 26 '15 at 19:15
So share your function, prepare structures using **[SQLFiddle](http://sqlfiddle.com)**. Without this info we cannot help. Using CTE/CROSS APPLY/CASE you can achieve a lot. — Lukasz Szozda, Sep 26 '15 at 19:16
I've updated the question. I don't think the content of the function is important. I just want to know if its possible to call it without using sql for a subset of data. I'm not familiar with sql which is why i used cursors, and i'm looking for advice — newbie_86, Sep 26 '15 at 19:25

Lukasz Szozda · Accepted Answer · 2015-09-26T20:25:02.190

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.

Call function for subset of rows

1 Answers1