ansaurus

Question

Answer 1

A:

At present, it does not seem to be possible to return a UDTT from a UDF. A UDF can return a table variable, or an inline table.

Returning an 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

Those are the only types of TVFs at the moment.

Aaronaught 2010-02-16 23:55:02

maybe there are tricks like returning cursor or smthg? It doesn't seem like a good idea to copy same table definitions in every function.

Sergej Andrejev 2010-02-17 00:05:04

@Sergej: No tricks, and even if you could return a cursor, you wouldn't want to. If you can write the functions inline, that is the best, because then the table definition is implicit. Unfortunately, SQL Server (and many other DBMSes) aren't as good at the DRY concept as programming languages are. Exception handling in SQL Server is the same way, a lot of copy and pasting.

Aaronaught 2010-02-17 00:06:31

I have to bare with it then

Sergej Andrejev 2010-02-17 00:10:13

ansaurus

tags:

views:

answers:

Returning table-type by function

related questions