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

Here’s my user-defined table type…

 [Bar] [INT],

This is what ive had to do in my table-valued function to return the type:

  • select the maximun item per year in MSSQL
  • Get Master key Password in Sql Server
  • Insert statement within IF Exists using other data
  • Using pivot in tsql
  • Paging in SQL Server problems
  • How to search Keywords like IN, OR, AND in a column with FullText Index in MSSQL Server 2008
  • CREATE FUNCTION [dbo].[GetFoos]
    RETURN @FooTypes TABLE ([Bar] [INT])
    INSERT INTO @FooTypes (1)

    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)

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


    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

        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

    RETURNS @tableVar TABLE
        FieldOne varchar (512),
        FieldTwo varchar(1024)
     --Modify your variable here

    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:


    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.