How to create and use a table valued function.

Problem: 

How to create a table result set Object in the FROM Clause based on a parameter passed.

Solution: 

Table Valued Function, CREATE FUNCTION

Functions are generally used for calculating business logic, encapsulate complex query and for simplifying large block of code to re-use.

Table Valued Function is a User-defined function that return a table data type can be powerful alternatives to views. A table-valued user-defined function can be used where table or view expressions are allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.

A table-valued user-defined function can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored procedures that return result sets cannot.

Functions also can be schema bound to secure the metadata inside.

Example:

I have an Employees table and I would like to select all columns in employees table by their job type. This is done by creating a table valued function and passing a parameter into the function to retrieve specified output. Please see the following code to create a sample table valued result set.

CREATE FUNCTION dbo.Fn_ReturnAllEmployeesbyEmployeeType

(@EmployeeType VARCHAR(50))

RETURNS TABLE

AS

RETURN

(

SELECT  [EmployeeID]

      ,[Name]

      ,[PhoneNumber]

      ,[EmployeeType]

      ,[Company_Id]

  FROM [dbo].[Employees]

  WHERE EmployeeType =@EmployeeType

 )

 select * from dbo.Fn_ReturnAllEmployeesbyEmployeeType('Developer')

Please see the below screenshot to see the table results from function output.