Search notes:

SQL Server: Functions cannot return UDTs (User defined types)

Unfortunately, in SQL Server, it's impossible to return (table of) *user defined types from a function. Thus, in order to come close to something like returning such a table, we have to resort to a table valued function.
This is demonstrated in the following.

Create a user defined type

The following create type statement creates a simple user defined type with two attributes. 
create type tq84_udt as table (
   attr_1  integer,
   attr_2  varchar(10)
);

go
Github repository about-MSSQL, path: /t-sql/user-defined-functions/table-valued/return-udt/create-udt.sql

Create a function

The following function can be passed a (table of) records that correspond to the user defined type and a string that specifies which records from the udt should be filter.
It uses a insert into statement with a like to actually select the matching rows into the variable that is returned and then returns this variable. 
create function filter_udt(
         @things tq84_udt
                 readonly  -- Note: table-valued parameters must be declare with the
     ,
     @attr_2_like varchar(max)
     )
     returns @ret table
     --
     --  SQL Server does not allow to return user defined types.
     --  Thus, we need to resort to a Multi statement table valued
     --  function which is why we have to duplicate the column
     --  definitions of someThings
       --
     (
        attr_1 integer,
        attr_2 varchar(10)
     )
as
begin

   insert into @ret
   select
      *
   from
      @things
   where
      attr_2 like '%a%';

   return;
end
;

go
Github repository about-MSSQL, path: /t-sql/user-defined-functions/table-valued/return-udt/create-function.sql

Using the function

Finally, the function is used: 
declare
   @things tq84_udt;

insert into @things values(1, 'foo');
insert into @things values(2, 'bar');
insert into @things values(3, 'baz');

select * from dbo.filter_udt(@things, '%a%');
--
-- attr_1      attr_2
-- ----------- ----------
-- 2           bar
-- 3           baz
Github repository about-MSSQL, path: /t-sql/user-defined-functions/table-valued/return-udt/use.sql

Index