SQL Server 2008 - How do i return a User-Defined Table Type from a Table-Valued Function?
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
-- 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))ASBEGIN --Modify your variable hereRETURNEND
Hopefully this will help somebody.
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.
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.