Wsrtjtyk

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

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.