Microsoft SQL Server - How To Create Table-Valued User-Defined Functions

A table-valued user-defined function is a function that returns a table (a set of rows) and can be used in FROM clause.

Quick Example:

   -- Create a table-valued user-defined function
   CREATE FUNCTION get_cities (@filter VARCHAR(70))
   RETURNS @cities TABLE   -- Table definition for result
   (
      name VARCHAR(90),
      state CHAR(2)
   )
   AS
   BEGIN
     -- Insert rows into the return table
     INSERT @cities   
     SELECT * FROM cities WHERE name LIKE @filter; 
 
     RETURN;
   END;
   GO  
 
   -- Call the function
   SELECT * FROM get_cities('%');