T-SQL: How to use a user defined function with an table valued parameter from within a select statement

Question

I have the following code to convert records with a column of integer into binary code:

CREATE TYPE T_Table AS table(MyColumn int);
GO

CREATE FUNCTION FUNC_Test(@table T_Table READONLY) RETURNS varbinary AS BEGIN

    ...

    RETURN <anything as varbinary>
END;
GO

SELECT
    X.ID
    ,dbo.FUNC_Test(
        (SELECT <anything as int> FROM <any table y> WHERE ID = X.ID)
    )
FROM
    <any table x> AS X
GROUP BY
    X.ID
;
GO

But this doesn't work. Why can't I use a Select statement as a parameter for the user defined function?

Is it possible without CLR?

Can't you just pass the X.ID as parameter and do the select in your function? — James Z, Jul 14 '15 at 10:37

score 1 · Answer 1 · answered Jul 14 '15 at 11:24

Parameter of the function FUNC_Test has to be of the T_Table type.

This will work:

DECLARE @T T_Table;
SELECT FUNC_Test(@T) AS b;

This will not work:

DECLARE @T TABLE (MyColumn int);
SELECT FUNC_Test(@T) AS b;

Operand type clash: table is incompatible with T_Table

Tested on SQL Server 2014 Express. I guess, it is one of the limitations of the table-valued parameters.

As pointed out in the comments, one possible workaround is to pass just ID as a parameter and perform the actual query inside the function.

T-SQL: How to use a user defined function with an table valued parameter from within a select statement

1 Answers1