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:

  • Aggregate function not allowed in Set statment in TSQL
  • SQL Server FOR XML Path make repeating nodes
  • SQL Server 2008R2 from Table to edges for Graph
  • insert control characters in nvarchar or varchar from SQL script?
  • Conversion of varchar into Datetime
  • JDBC connection failed, error: TCP/IP connection to host failed
  • 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).

    5 Solutions collect form web for “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)
    )
    AS
    BEGIN
     --Modify your variable here
    RETURN
    END
    

    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.

    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.

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

    Restrictions

    Table-valued parameters have the following restrictions:

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

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

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.