Table type return by function

Question

Can custom table value (or any other function) return a table type in SQL Server 2008?

Answer 1

It is not currently possible to return UDTT from UDF. UDF can return a table variable or an embedded table.

Return inline table:

CREATE FUNCTION dbo.MyFunc1 RETURNS TABLE AS RETURN SELECT <columns> FROM <table> WHERE <conditions> 

Returning a table variable:

 CREATE FUNCTION dbo.MyFunc2 RETURNS @Tbl TABLE ( ID int, Name varchar(50) ) AS BEGIN INSERT @Tbl (ID, Name) SELECT ID, Name FROM <table> WHERE <conditions> RETURN END 

These are the only types of TVF at the moment.