I’d like to enforce the user-defined table type returned by a sql function, making the following code possible (I haven’t of course defined the function so it won’t execute):

CREATE TYPE Test.TestType
AS TABLE
(
C1 int NOT NULL
,C2 int NOT NULL
)

DECLARE @Vals Test.TestType
INSERT INTO @Vals SELECT * FROM Test.TestTypeFunction()

Perhaps I’d better quickly justify the use of SELECT * FROM. I’ve had the misfortune to be a maintenance programmer on an application which made a lot of use of this construct so I don’t lightly drop it into my code. For this statement though, the INSERT and SELECT column sets are guaranteed compatible – provided the function returns a table of the same table type as the caller’s variable.

So everything’s fine as long as we can guarantee that the function returns the expected table type. This function will have to be a “Multi-statement table-valued function”, since a table-type variable has to be declared to hold the data returned. That won’t be allowed in an inline function. Syntax for declaring a multi-statement table-valued function is as follows:

CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] [READONLY] } [ ,...n ] ] )
RETURNS @return_variable TABLE <table_type_definition>
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN END [ ; ]

Here’s where a small problem pops up. The bold line shows where you make the table declaration for the data returned. Instead of including a table definition, I wish I could write the following:
RETURNS @return_variable TABLE Test.TestType
…but apparently this isn’t possible. Of course I could simply paste in the definition of the table type. I would then have lost my guarantee of table type compatibility – anyone would be able to redefine the table in the function.

I think it’s very useful to have that guarantee so I don’t mind a little bit of kludgery in order to get it. The following script shows how the function can be tied in to the data type even though my handy syntax isn’t available. I’m using the WITH SCHEMABINDING option so that the table type can’t be dropped without re-editing the function. This means that an explicit column list has to be provided within the function but that’s not a problem since we don’t want “SELECT *” becoming a habit…

CREATE TYPE Test.TestType
AS TABLE
(
C1 int NOT NULL
,C2 int NOT NULL
)

GO

CREATE FUNCTION Test.TestTypeFunction
(
)
RETURNS
@ReturnValue TABLE
(
C1 int NOT NULL
,C2 int NOT NULL
) WITH SCHEMABINDING /*
prevents Test.TestType being dropped
without amending this function
*/
AS
BEGIN
DECLARE @T Test.TestType
INSERT INTO @ReturnValue
SELECT 1, 1
/*


UNION ALL ensures function output matches Test.TestType column list

*/

UNION ALL

SELECT C1, C2 FROM @T

RETURN
END

GO

/*CALL THE FUNCTION*/

DECLARE @Vals Test.TestType

INSERT INTO @Vals SELECT* FROM Test.TestTypeFunction()

SELECT* FROM @Vals

GO

DROP FUNCTION Test.TestTypeFunction
DROP TYPE Test.TestType

I think that being able to call the function as shown makes the jiggery-pokery worthwhile. The comments inside the function are needed though. Anyone seeing this for the first time may well be confused or irritated to see a UNION with an empty table.

Incidentally, with a stored procedure there’s no problem achieving the same goal. A variable of the table type you want can easily be defined inside the procedure. Instead of returning results to the client, the SELECT statement/s populate the variable. The output from the procedure then becomes a SELECT from it.