SQL Server 2008 - How do i return a User-Defined Table Type from a Table-Valued Function?

Question

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?

EDIT

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

Answer 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

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