問題描述
多語句 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模板網！