SQL Server 2008 - 如何从表值函数返回用户定义的表类型? [英] SQL Server 2008 - How do i return a User-Defined Table Type from a Table-Valued Function?

问题描述

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

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

基本上，我必须在函数的 RETURN 语句中重新声明我的类型定义.有没有办法可以简单地在 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?

我原以为这会奏效:

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

在 MSDN/Google 上找不到关于此的任何帮助......有人吗?

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

编辑

我取消了我的答案的标记，并提出了这个问题 - 因为我在 6 个月后遇到了同样的情况.

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

有谁知道是否可以从表值函数返回用户定义的表类型?如果没有，除了我所做的之外，还有更好的解决方法吗?(再次重新声明类型).

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).

推荐答案

好的 - 所以无法完成.

Ok - so it cant be done.

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

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

仍然 - 希望这个问题在下一版 SQL Server 中得到纠正.

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

这篇关于SQL Server 2008 - 如何从表值函数返回用户定义的表类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！