Is there any code on SQL Server similar to Oracle's PIPE ROW?

Question

Which code is similar to Oracle's PIPE ROW in SQL Server? I'm Studying Oracle for the fist time, and just faced with "PIPE ROW" in a string of a code, what's the use of it?

Answer 1

In Oracle a function is a stored procedure which returns a value:

create or replace function get_number
  return number is
begin
  return 42;
end;
/

select get_number
from dual
/

But the returned value doesn't have to be a scalar. If it is a nested table we can access it with the table() function and treat it like an actual table in a query's FROM clause:

create or replace function get_numbers
  return sys.odcinumberlist is
begin
  return sys.odcinumberlist (23, 42);
end;
/

select * 
from table(get_numbers)
/

But if the function is a pipelined function we can use a pipe row call to output any value we like. So we can use programmatic logic to determine the output, for instance generating a list of numbers from 1 to n:

create or replace function get_n_numbers (p_n in number)
  return sys.odcinumberlist pipelined is
begin
  for n in 1..p_n loop
    pipe row(0+n);
  end loop;

  return;
end;
/

select * 
from table(get_n_numbers(3))
/

Here is a demo on db<>fiddle.

Pipelined functions seem like an incredibly powerful feature but in practice the use cases for them are rather niche. For instance we can use one to split a string into distinct tokens but using regex or xslt is probably faster.