T-SQL：如何在select语句中使用带有表值参数的用户定义函数

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?

但这不起作用。为什么我不能将Select语句用作用户定义函数的参数?

Is it possible without CLR?

没有CLR可能吗?

1 个解决方案

#1

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

函数FUNC_Test的参数必须是T_Table类型。

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 *: table is incompatible with T_Table

操作数类型冲突:表与T_Table不兼容

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

在SQL Server 2014 Express上测试。我想,这是表值参数的局限之一。

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.

正如评论中指出的,一种可能的解决方法是仅将ID作为参数传递并在函数内执行实际查询。

#1