Types of Functions in SQL Server 2008 R2

There are 3 types of functions in SQL Server 2008 R2 :

"Table Valued Function"

Understanding Inline Table Valued Function (ITVF)

   Link - "Understanding ITVF with example

Understanding Multistatement Table Valued Function (MSTVF)
   Link - "Understanding MSTVF with example"

"Scalar Valued Function" 


Understanding Multistatement Scalar Valued Function (MSTVF)
   Link - "Understanding MSSVF with example"


Understanding Inline Scalar Valued Function (ISVF)
   Link - "Understanding ISVF with example"


"System Function"

     Return a single data value of the type defined in the  RETURNS clause

     For an inline scalar function, there is no function body; the scalar value is the result of a single statement. 
     For a multistatement scalar function, the function body, defined in a BEGIN...END block, contains a series of Transact-SQL statements that return the single value. The return type can be any data type except text, ntext, image, cursor, and timestamp.

Inline user-defined functions follow these rules:

• The RETURNS clause contains only the keyword table. You do not have to define the format of a return variable, because it is set by the format of the result set of the SELECT statement in the RETURN clause.
• There is no function_body delimited by BEGIN and END.
• The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.
• The table-valued function accepts only constants or @local_variable arguments

Multistatement Scalar Valued Function

For a Multistatement Scalar-valued Function, the function body, defined in a BEGIN...END block, contains a series of T-SQL statements.
The parameter returned from the function is a scalar value.


/* Code : MSSV Function containing multiple statement */
CREATE FUNCTION MyFunc_SV_MultiStmnt( @inEmployeeKey INT )
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @temp_var VARCHAR(15)
SELECT @temp_var = FirstName
FROM dbo.DimEmployee
WHERE EmployeeKey=@inEmployeeKey

RETURN (@temp_var)
END


/* Call above MSSV function */
SELECT dbo.MyFunc_SV_MultiStmnt(2)


** Unlike the function call for Multistatement Table-Valued Function, where the function name is mentioned in the from clause, the function call for Multistatement Scalar-Valued Function doesn't contain any from clause.
** The function name is a part of the SELECT clause.

Multistatement Table Valued Function

For a multistatement table-valued function, the function body, defined in a BEGIN...END block, contains a series of Transact-SQL statements that build and insert rows into the table that will be returned.

• Explicitly defines the structure of the table to return.
• Defines column names and datatypes in the RETURNS clause.
• Able to house more complicated and numerous T0SQL logic blocks. 

/* Code: MTV function containing multiple statements */

CREATE FUNCTION dbo.MyFn_MultiStmnt()

RETURNS @temp_table table

(

column2 int

)

AS

BEGIN

INSERT INTO @temp_table(column2)

SELECT DISTINCT EmployeeKey

FROM dbo.DimEmployee

RETURN

END

/*Call above MTV function*/

SELECT * 
FROM dbo.MyFn_MultiStmnt()

** The function contains more than one statement in the function body. Unlike Inline Table Valued function, where only one statement can be written, in Multistatement Table Valued function more than one statement can be written.

** Following function body code would give ERROR when used with an Inline Table Valued Function.

/* Code:MTV function containing multiple statement*/
CREATE FUNCTION dbo.MyFn_MultiStmnt()

RETURNS @temp_table table
(
column2 int
)
AS
BEGIN
INSERT INTO @temp_table(column2)
SELECT DISTINCT EmployeeKey
FROM dbo.DimEmployee
ORDER BY 1

INSERT INTO @temp_table(column2)
SELECT DISTINCT EmployeeKey+1
FROM dbo.DimEmployee
ORDER BY 1

RETURN
END

Inline Table Valued Function

/* Code : Create an Inline Table Valued Function */
CREATE FUNCTION dbo.MyFn_CustomerNamesInRegion()
RETURNS table
AS
RETURN (
        SELECT DISTINCT *
        FROM dbo.DimEmployee
       );
/* Call Above Function */
SELECT *
FROM dbo.MyFn_CustomerNamesInRegion()


- Inline Function Rule

• The RETURN clause contains a single statement in parentheses. Creating a function like below would result in "FAILURE" since it has more than one statement in the return clause :
  CREATE FUNCTION dbo.MyFn_CustomerNamesInRegion()RETURNS table
  AS
  RETURN (
          INSERT INTO dbo.temp2(column2)
  SELECT * 
  FROM dbo.DimEmployee
         );
  
  
• The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.
• There is no function_body delimited by BEGIN and END.
• The table-valued function accepts only constants or @local_variable arguments.