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

inline table-valued function in sql server
sql function return user-defined table type
call table-valued function in select statement
how to declare a variable in table-valued function in sql server 2008
how many different types of table-valued udfs are there
table-valued function vs view
how to select user defined table type in sql server
insert data into user-defined table type in sql server

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.

SQL Server Table-Valued Function By Practical Examples, The return type of a table-valued function is a table, therefore, you can use the The syntax is similar to the one that creates a user-defined function. A table-valued function is a user-defined function that returns data of a table type. The return type of a table-valued function is a table, therefore, you can use the table-valued function just like you would use a table. Creating a table-valued function. The following statement example creates a table-valued function that returns a list of

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.

Returning a User-Defined Table Type from a Multi-Statement Table , Returning a User-Defined Table Type from a Multi-Statement SQL Server 2008 - General; Returning a User-Defined Table Type from I created a Multi-​statement Table-valued Function (simplified for the Hopefully, a future version of SQL Server (not 2012 RTM) will enhance the abilities of table-valued  As far as I understand Microsoft's MSDN article here, those user-defined table types are only available as read-only parameters to stored procedures or stored functions.. It doesn't mention anything that they could be used to be returned from a user-defined function, unfortunately - so I guess you're right - it's not possible (at least not now).

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.

How to: Use Table-Valued User-Defined Functions, Because the return type of a table-valued function is Table , you can use a table-​valued function anywhere in SQL that you can use a table. Inline Table-Valued Functions. A table-valued function is a function that returns data of table type. In this example we will create an inline table-valued function that will retrieve records of all the students whose DOB is less than the DOB passed to the function. Execute the following script on your server.

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.

return a User Defined Table Type in SQL Server? - MSDN, If you use a multi-statement table-valued function, that is exactly what it is can you this new feature as return type in user defined functions? Creates a user-defined function in SQL Server and Azure SQL Database. A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table.

User Defined Table Types And Table Valued Parameters, In this article you will learn about User Defined Table Types and Before SQL Server 2008, it was not possible to pass a table variable in stored data to a stored procedure or a function without creating a temporary table or  B) Table Value Function. A table-valued user-defined function returns a rowset instead of a single scalar value. Can be invoked in the FROM clause of a SELECT statement, just as we would a table or view. A table-valued function can almost be thought of as a view that accepts parameters, so the result set is determined dynamically.

SQL Server inline table-valued functions, The simple definition of the table-valued function (TVF) can be made such like that; a user-defined function that returns a table data type and also it can accept article, SQL Server built-in functions and user-defined scalar functions, to gain The above code part specifies that the function will return a table. The concepts of User-Defined Table Types (UDTTs) and Table-Valued Parameters (TVPs) were introduced in SQL Server 2008. Before SQL Server 2008, it was not possible to pass a table variable in stored procedure as a parameter, after SQL Server now we can pass Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so

Table-Valued Functions in SQL Server, User Defined Functions can return scalar values or table type data. In this article we're going to take a look at the functions that return table type  Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Comments
  • Confirmed still not possible with 2014.
  • This is a better answer than the currently marked one.
  • I know this is an old question, but IMVHO you should unmark your answer and mark the answer from James as it explains and solves the issue nicely.
  • So i guess the answer is still no. :) Dang. All im trying to achieve here is a type of "contract" between the UDF and the code that is calling it. Keeps the T-SQL "D-R-Y". There's not much point even using the UDTT here if i can't return it from the function. Oh well. Thanks anyway.
  • I know i cant return a UDT from a Scalar function. But in regards to Table functions - you are able to return tables (or table variables). A UDT is just another table so cant see why you cant return it. Oh well, maybe in the next version of SS. =) Im doing this because i have a table variable which was being duplicated amongst numerous functions/procs. Therefore i put it in an UDT to prevent being "DRY". Easier to maintain, etc.