SQL Server 2008 -如何从表值函数返回用户定义的表类型?

Here's my user-defined table type...

这是我的用户定义表类型…

CREATE TYPE [dbo].[FooType] AS TABLE(
 [Bar] [INT],
)

This is what ive had to do in my table-valued function to return the type:

这就是我在表值函数中要返回的类型:

CREATE FUNCTION [dbo].[GetFoos]
RETURN @FooTypes TABLE ([Bar] [INT])
INSERT INTO @FooTypes (1)
RETURN

Basically, im having to re-declare my type definition in the RETURN statement of the function. Isnt there a way i can simply declare the type in the RETURN statement?

基本上，我必须在函数的返回语句中重新声明我的类型定义。难道我就不能在返回语句中声明类型吗?

I would have thought this would work:

我本以为这行得通:

CREATE FUNCTION [dbo].[GetFoos]
RETURN @FooTypes [FooType]
INSERT INTO @FooTypes (1)
RETURN

Cannot find any help on MSDN/Google regarding this....anyone?

找不到任何帮助在MSDN /谷歌对此....有人知道吗?

EDIT

编辑

I unmarked my answer, and bumping this question - as i am encountering the same scenario 6 months later.

6个月后，当我遇到同样的情况时，我没有标注我的答案，并遇到了这个问题。

Does anyone have any idea if it's possible to return a user defined table type from a table valued function? If not, is there a better workaround other than what i have done? (re-declare the type again).

有没有人知道是否可以从表值函数中返回用户定义的表类型?如果没有，除了我所做的，还有更好的方法吗?(再次re-declare类型)。

5 个解决方案

#1

Even though you can not return the UDTT from a function, you can return a table variable and receive it in a UDTT as long as the schema match. The following code is tested in SQL Server 2008 R2

即使不能从函数中返回UDTT，也可以返回一个表变量并在UDTT中接收它，只要模式匹配就行。下面的代码在SQL Server 2008 R2中进行了测试

-- Create the UDTT

——创建一个通用

CREATE TYPE dbo.MyCustomUDDT AS TABLE
(
    FieldOne varchar (512),
    FieldTwo varchar(1024)
)

-- Declare your variables

——声明变量

DECLARE @uddt MyCustomUDDT;
DECLARE @Modifieduddt MyCustomUDDT;

// Call the function

/ /调用函数

INSERT INTO @Modifieduddt SELECT * FROM dbo.MyUDF(@uddt);

Function signature

函数签名

CREATE FUNCTION dbo.MyUDF(@localUDDT MyCustomUDDT)
RETURNS @tableVar TABLE
(
    FieldOne varchar (512),
    FieldTwo varchar(1024)
)
AS
BEGIN
 --Modify your variable here
RETURN
END

Hopefully this will help somebody.

希望这能帮助某些人。

#2

Ok - so it cant be done.

好的，这是不可能的。

Easy enough to duplicate the table definition in the return type (with the use of scripting).

很容易在返回类型中复制表定义(使用脚本)。

Still - hopefully this issue gets rectified in the next version of SQL Server.

这个问题有望在下一个版本的SQL Server中得到纠正。

#3

The syntax for CREATE FUNCTION indicates that the only way to define a table return type is by listing columns and types, a <table_type_definition>. Even SQL Server "Denali" has the same definition for <table_type_definition>. Although strangely, it's syntax doesn't include multi-statement Table valued functions, or anything else that references this fragment.

CREATE函数的语法表明，定义表返回类型的惟一方法是列出列和类型，即。甚至连SQL Server“Denali”对于也有相同的定义。尽管奇怪的是，它的语法不包括多语句表值函数，也不包含引用此片段的任何其他内容。

#4

I do not believe this is possible. You cannot use a UDTT as the return type of a Scalar-Valued Function because it is not a scalar value. You also cannot replace the table declaration of a Table-Valued Function with a UDTT. Repeating the table definition seems to be the only option. If we knew why you were doing this, perhaps we could find an alternative.

我认为这是不可能的。不能使用UDTT作为标量值函数的返回类型，因为它不是标量值。您也不能用UDTT替换表值函数的表声明。重复表定义似乎是唯一的选择。如果我们知道你为什么这样做，也许我们可以找到一个替代方案。

#5

Nope, afraid not at the moment, as per this question. And the following from msdn:

不，恐怕现在不行，就像这个问题一样。msdn:

Restrictions

限制

Table-valued parameters have the following restrictions:

表值参数有以下限制:

SQL Server does not maintain statistics on columns of table-valued parameters.

SQL Server不维护表值参数列的统计信息。
Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

表值参数必须作为输入只读参数传递给Transact-SQL例程。不能执行DML操作，如在例程的主体中对表值参数进行更新、删除或插入。
You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.

不能将表值参数用作SELECT或INSERT EXEC语句的目标。表值参数可以在SELECT INTO的FROM子句中，也可以在INSERT EXEC字符串或存储过程中。

#1