SQL Server 2008:多语句 UDF 能否返回 UDT?

问题描述

多语句 UDF 是否有可能返回用户定义的表类型，而不是在其返回参数中定义的表?

Is it possible that a multi-statement UDF return a User Defined Table Type, instead of a table that is defined within it's return param?

所以代替:

CREATE FUNCTION MyFunc 
(
    @p1 int, @p2 char
)
RETURNS 
@SomeVar TABLE 
(
    c1 int
)
AS

我想做:

CREATE FUNCTION MyFunc 
(
    @p1 int, @p2 char
)
RETURNS 
@SomeVar MyTableType
AS

这样做的原因是我的函数内部调用了其他函数，必须传入MyTableType UDT，即使我在RETURN表类型中定义了完全相同的表定义，也会抛出操作数冲突错误.

The reason for this is that inside my function I call other functions and have to pass in MyTableType UDT, even if I define exactly the same table definition in the RETURN table type, it will throw an operand clash error.

推荐答案

我能想到的最好办法是声明一个您的类型的表变量本地函数，并在整个代码中使用它.然后在 RETURN 语句之前对参数表执行 INSERT...SELECT 操作.

The best that I could come up with was to declare a table variable of your type local to the function and use that throughout your code. Then do an INSERT...SELECT into the parameter table right before the RETURN statement.

到目前为止，我已经避免使用用户定义的类型.虽然它们看起来很有前景，但由于能够在一个位置更改类型而不是在任何地方更改数据类型，但由于此类问题，它们在生产力和维护方面似乎从未实现过.

I've avoided user-defined types so far. While they seem promising, with the ability to change the type in one location instead of changing data types everywhere, they just never seem to deliver when it comes to productivity and maintenance because of issues like these.

这篇关于SQL Server 2008:多语句 UDF 能否返回 UDT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！