Passing a table expression to a table-parameterized function

Question

I have the following user defined function:

CREATE FUNCTION dbo.GetConcatenatedWithKeyAsInt32(@Values dbo.IndexValue READONLY)
RETURNS TABLE 
WITH SCHEMABINDING
AS 
RETURN (
  SELECT
  [Id],
   -- The definition of [Names] is not really important to this question!
  [Names] = stuff((
    select ', ' + Value
    from @Values AS xt
    where xt.Id = t.Id
    for xml path(''), TYPE
  ).value('.[1]','varchar(max)'), 1, 2, '')
  FROM @Values AS t GROUP BY t.Id);

The parameter is of a user-defined table type:

CREATE TYPE IndexValue AS TABLE ( Id int, Value VARCHAR(max) );

I'm struggling to call this function.

I have found examples where people call such a function on an actual, physical table (or view), but surely it will be possible to use it in a select expression directly, won't it?

I tried:

SELECT *
FROM dbo.GetConcatenatedWithKeyAsInt32(
  SELECT c.Id AS Id, a.City AS value
  FROM Customers c
  JOIN Addresses a ON c.Id = a.CustomerId
);

SQL Server doesn't like this:

Incorrect syntax near the keyword 'SELECT'.
Incorrect syntax near ')'

Is this possible? If so, what's the correct syntax?

Or do I really need to create a temporary table or view for

SELECT c.Id AS Id, a.City AS value
FROM Customers c
JOIN Addresses a ON c.Id = a.CustomerId

first?

Answer 1

No, it is not possible to pass a query expression by reference as opposed to by value (the syntax you are attempting to use is quite simply not supported). So, if that is the gist of your question, then the answer is no.

But allow me to add some other advice about how you could and/or should accomplish what it seems to be that you wish to accomplish.

First, please always use the schema prefix when creating or referencing objects.

CREATE TYPE dbo.IndexValue AS TABLE ( Id int, Value VARCHAR(max) );

Next, use an inline table-valued function rather than a multi-statement table-valued function, use SCHEMABINDING, and always specify a length for variable-length types like nvarchar (though not sure why you're using nvarchar in the function when that's not possible given the input):

CREATE FUNCTION dbo.GetConcatenatedWithKeyAsInt32
(
  @Values dbo.IndexValue READONLY
)
RETURNS TABLE
WITH SCHEMABINDING
AS 
  RETURN (SELECT
    [Id],
    [Names] = stuff((
    select ', ' + CAST(Id AS nvarchar(255)) as [text()]
    from @Values AS xt
    where xt.Id = t.Id
    for xml path('')
    ), 1, 2, '')
  FROM @Values AS t GROUP BY t.Id);
GO

Now you can use this function no problem:

DECLARE @x dbo.IndexValue;
INSERT @x VALUES(1,'hoobah'),(1,'floobah'),(2,'a'),(2,'x'),(2,'y'),(3,'me');
SELECT Id, Names FROM dbo.GetConcatenatedWithKeyAsInt32(@x);

Though I have to suspect there is yet another logic error in your function, and that is that you should have applied the XML operations to the Value column, not the ID. The output of this is:

Id   Names
---- -------
1    1,1
2    2,2,2
3    3

If that is what you actually meant to do, then the function should have further changes, most notably to correct the input handling and also to protect the data from entities that are unsafe for XML (e.g. >).

ALTER FUNCTION dbo.GetConcatenatedWithKeyAsInt32
(
  @Values dbo.IndexValue READONLY
)
RETURNS TABLE
WITH SCHEMABINDING
AS 
  RETURN (SELECT
    [Id],
    [Names] = stuff((
    select ', ' + Value
    from @Values AS xt
    where xt.Id = t.Id
    for xml path(''), TYPE
    ).value('./text()[1]','varchar(max)'), 1, 2, '')
  FROM @Values AS t GROUP BY t.Id);
GO

Now this works much nicer:

DECLARE @x dbo.IndexValue;
INSERT @x VALUES(1,'hoo&bah'),(1,'floo<b>ah'),(2,'a'),(2,'x'),(2,'y'),(3,'me');
SELECT Id, Names FROM dbo.GetConcatenatedWithKeyAsInt32(@x);

Output:

Id   Names
---- ------------------
1    hoo&bah, floo<b>ah
2    a, x, y
3    me

As for the error message, @Lamak is correct, you cannot pass a query into the argument of a function, you need to incorporate the function call within the query, e.g. using CROSS APPLY or OUTER APPLY.

As for the updated code sample you're trying to get to work, I don't know why you're using a function for this. Why not just:

SELECT DISTINCT c.Id, Names = STUFF((SELECT ', ' + a.City
     FROM dbo.Addresses AS a
     WHERE a.CustomerId = c.Id GROUP BY a.City
     FOR XML PATH(''), TYPE
).value('./text()[1]','varchar(max)'), 1, 2, '')
FROM dbo.Customers AS c;

If you're trying to make it generic, then I guess you could revert back to your inefficient multi-statement TVF. Here is the syntax to do that:

CREATE FUNCTION dbo.GetConcatenatedWithKeyAsInt32_b
(
  @Values dbo.IndexValue READONLY
)
RETURNS @ret TABLE
(
   Id int PRIMARY KEY NOT NULL, 
   value nvarchar(max) NOT NULL
)
WITH SCHEMABINDING
AS 
BEGIN
  INSERT @ret SELECT
    Id, Names = STUFF((SELECT ', ' + Value
     FROM @Values AS xt 
     WHERE xt.Id = t.Id GROUP BY Value
     FOR XML PATH(''), TYPE
    ).value('./text()[1]','varchar(max)'), 1, 2, '')
  FROM @Values AS t GROUP BY t.id;

  RETURN;
END
GO

Then you could call it - again, since you want the generic table type as input - after stuffing the results of the join into a declared table variable. There is no way to just pass your SQL query to the function, sorry. You need to pass a table, not a query.

DECLARE @x dbo.IndexValue;

INSERT @x(Id, Value) 
  SELECT c.Id, a.City
  FROM dbo.Addresses AS a
  INNER JOIN dbo.Customers AS c
  ON a.CustomerId = c.Id;

SELECT * INTO #x FROM dbo.GetConcatenatedWithKeyAsInt32_b(@x);

Other than obfuscating away the XML logic, this doesn't really gain you anything and probably slows down this query substantially. You seem to be going through a lot of extra layers for no apparent reason (inserting the result of the join into the table type, then using the table type to call the function, then inserting those rows into a declared table inside the function, then performing the XML operation against that table).

Don't look at the execution plan to see which one is "faster" - it assigns all kinds of approximate costs and doesn't know how to properly evaluate and cost certain XML operations. Instead, just time them!

SET NOCOUNT ON;
SELECT GETUTCDATE();
GO

    SELECT c.Id, Names = STUFF((SELECT ', ' + a.City
         FROM dbo.Addresses AS a
         WHERE a.CustomerId = c.Id GROUP BY a.City
         FOR XML PATH(''), TYPE
    ).value('./text()[1]','varchar(max)'), 1, 2, '')
    INTO #x
    FROM dbo.Customers AS c;

    DROP TABLE #x;

GO 5000
SELECT GETUTCDATE();
GO
    DECLARE @x dbo.IndexValue;

    INSERT @x(Id, Value) 
      SELECT c.Id, a.City
      FROM dbo.Addresses AS a
      INNER JOIN dbo.Customers AS c
      ON a.CustomerId = c.Id;

    SELECT * INTO #x FROM dbo.GetConcatenatedWithKeyAsInt32_b(@x);
    DROP TABLE #x;
GO 5000
SELECT GETUTCDATE();
GO

Results:

XML:           16,780 ms
Your approach: 32,230 ms

Even if I change:

    INSERT @x(Id, Value) 
      SELECT c.Id, a.City

To:

    INSERT @x(Id, Value) 
      SELECT DISTINCT c.Id, a.City

(so that the function has less data to deal with, if there are duplicates), I only shave a little time off (you still need the GROUP BY in the function, IMHO, to protect yourself from queries that will feed it):

XML:           16,824 ms
Your approach: 29,576 ms

I wasn't making stuff up when I told you that DRY is not always beneficial in SQL Server. Functions are not just pointers, they have substantial overhead.

Answer 2

In answer to your specific questions:

Is this possible? If so, what's the correct syntax? Or do I really need to create a temporary table or view [...] first?

The way you're trying to do this is not supported by SQL Server. The way that is would be to allocate a variable of the user-defined type, and SELECT your values into that variable:

declare @values as dbo.IndexValue

insert into @values
SELECT c.Id AS Id, a.City AS value
  FROM Customers c
  JOIN Addresses a ON c.Id = a.CustomerId

select * from dbo.GetConcatenatedWithKeyAsInt32(@values)

I believe this is supported as of SQL Server 2008.

It might be similar to creating a temporary table, but I believe this is the only way to achieve what you're asking.