1

I have a custom type and a table, and I want to reuse this as a return result for several table functions. I want to declare the type rather than recreate the definition. So the start of the function would look like:

CREATE Function Function(@Param1 INT, @Param2 INT)
RETURNS TABLE MyTableType
AS
BEGIN
...

This does not seem possible - which strikes me as a deficiency. Are there any suggestions?

0

1 Answer 1

1

No, it's not possible.

The syntax grammar is quite clear:

-- Syntax for Transact-SQL multi-statement table-valued functions.  
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN
    END
[ ; ]

You can see the line RETURNS @return_variable TABLE <table_type_definition> only allows a TABLE definition, not a table type:

<table_type_definition> ::=
( { <column_definition> <column_constraint>
  | <computed_column_definition> }
    [ <table_constraint> ] [ , ...n ]
)

Etc.

Having said that, Multi-Statement Table Valued Functions are usually really bad for performance, and can nearly always be rewritten as Inline TVFs instead. So you won't need a table definition anyway, as the single SELECT statement does that automatically.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.