SQL Server 2008 - How do i return a User-Defined T

问题:

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

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

回答2:

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.

回答3:

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.

回答4:

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.

回答5:

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.