SQL Server: Table valued functions

Two variants

Inline table valued functions

create function u.istv (@par int) returns table
as return
  select
    abc,
    def,
    ghi
  from
    t_bla
  where
    id = @par
;

select * from u.istv(42);

Multi-statement table valued functions

create function mstvf(@p integer)
  returns @ret table(col integer)
as
begin
  declare @v integer;

  set @v = 10 * @p; insert into @ret(col) values (@v);
  set @v = 20 * @p; insert into @ret(col) values (@v);

  return;
end;

select * from dbo.mstvf(4);

create table src(id integer, val varchar(30));

insert into src values(1, 'one'  );
insert into src values(2, 'two'  );
insert into src values(3, 'three');

go

create function select_into(@p integer)
  returns @ret table(id integer, val varchar(30))
as
begin
  insert into
    @ret
  select *
  from
  src
  where
    id < @p;

  return;
end;
go

select * from dbo.select_into(3);
go

drop function select_into;
go

drop table src;
go

See also