能否在SQL CLR用户定义类型中创建表值方法？

Question

我有一个CLR UDT，它可以从表值方法中受益，类似于xml.nodes()：

-- nodes() example, for reference:
declare @xml xml = '<id>1</id><id>2</id><id>5</id><id>10</id>'
select c.value('.','int') as id from @xml.nodes('/id') t (c)

我希望我的UDT有类似的功能：

-- would return tuples (1, 4), (1, 5), (1, 6)....(1, 20)
declare @udt dbo.FancyType = '1.4:20'
select * from @udt.AsTable() t (c)

有没有人对此有经验？任何帮助都将不胜感激。我尝试了一些方法，但都失败了。我寻找文档和示例，但没有找到。

是的，我知道我可以创建以我的UDT为参数的表值UDF，但我更希望将所有内容都捆绑在一个单一类型中，面向对象风格。

编辑

Russell Hart发现文档说明不支持表值方法，并修复了我的语法以产生预期的运行时错误（见下文）。

在VS2010中，在创建新的UDT后，我在结构定义的末尾添加了这个：

[SqlMethod(FillRowMethodName = "GetTable_FillRow", TableDefinition = "Id INT")]
public IEnumerable GetTable()
{
    ArrayList resultCollection = new ArrayList();
    resultCollection.Add(1);
    resultCollection.Add(2);
    resultCollection.Add(3);
    return resultCollection;
}

public static void GetTable_FillRow(object tableResultObj, out SqlInt32 Id)
{
    Id = (int)tableResultObj;
}

这个构建和部署成功了。但是在SSMS中，我们得到了一个运行时错误，正如我们所预料的（如果不是逐字逐句的话）：

-- needed to alias the column in the SELECT clause, rather than after the table alias.
declare @this dbo.tvm_example = ''
select t.[Id] as [ID] from @this.GetTable() as [t]

Msg 2715, Level 16, State 3, Line 2
Column, parameter, or variable #1: Cannot find data type dbo.tvm_example.
Parameter or variable '@this' has an invalid data type.

看起来这是不可能的。即使可能，考虑到在SQL Server中更改CLR对象的限制，这也可能不是明智之举。

话虽如此，如果有人知道绕过这个特定限制的技巧，我会相应地提高悬赏金额。

- Peter Radocchia

Answer 1

您已经对表进行了别名，但没有对列进行别名。请尝试：

declare @this dbo.tvm_example = ''
select t.[Id] as [ID] from @this.GetTable() as [t]

根据文档http://msdn.microsoft.com/en-us/library/ms131069(v=SQL.100).aspx#Y4739，这个应该会因错误类型而无法通过。SqlMethodAttribute类继承自SqlFunctionAttribute类，所以 SqlMethodAttribute从SqlFunctionAttribute继承了FillRowMethodName和TableDefinition字段。这意味着可以编写表值方法，但实际上不行。方法可以编译且程序集部署成功，但在运行时会出现关于IEnumerable返回类型的错误，错误信息如下："Method, property, or field '' in class '' in assembly '' has invalid return type." 可能是为了避免支持这样的方法。如果您使用方法更新更改程序集，则可能会对UDT列中的数据造成问题。适当的解决方案是拥有一个最小化的UDT，然后再单独创建一个方法类来陪伴它。这将确保灵活性和完全功能的方法。XML节点方法不会改变，因此不受相同的实现限制。希望这能帮助Peter并祝他好运。

- Russell Hart

---

@PeterRadocchia和Russell：请查看（并投票支持）我对将此功能添加到SQL Server的建议：允许CLR UDT从方法（SqlMethod）返回IEnumerable / Table / TVF，就像XML.nodes()一样。我还在我的答案中指出了这一点，这似乎是一个重复的问题：在用户定义类型上创建表方法（如XML数据类型上的'nodes'）。 - Solomon Rutzky