Can T-SQL function return user-defined table type?

sql-server tsql sql-server-2008 function user-defined-functions
abatishchev picture abatishchev · Mar 23, 2010 · Viewed 14.8k times · Source

I have my own type:

CREATE TYPE MyType AS TABLE
(
    foo INT
)

and a function receiving it as a parameter:

CREATE FUNCTION Test
(
    @in MyType READONLY
)
RETURNS @return MyType
AS
...

can it return MyType or only TABLE repeating MyType's structure:

CREATE FUNCTION Test
(
    @in MyType READONLY
)
RETURNS @return TABLE (foo INT)
AS
...

?

Answer

marc_s picture marc_s · Mar 23, 2010

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

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More