Table-valued - Functions

---

Table-valued User Defined Functions in SQL Server

A Table Valued User Defined Function accepts zero or more parameters and return a table variable. This type of function is special because it returns a table that you can query the results of a join with other tables.

A Table Valued function is further categorized into two types:

* Inline table valued functions

* Multi-statement table valued functions

1.Inline Table Valued Functions

An Inline Table Valued Function in SQL contains a single statement that must be a SELECT statement. The result of the query becomes the return value of the function. There is no need for a BEGIN-END block in an Inline function.

Syntax

CREATE FUNCTION function-name (Parameters)  
RETURNS return-type  
AS  
RETURN  

2.Multi-Statement Table Valued Function

A Multi-Statement contains multiple SQL statements enclosed in BEGIN-END blocks.

-> In a Multi-Statement Table valued function the return value is declared as a table variable and includes the full structure of the table to be returned.

-> The RETURN statement is without a value and the declared table variable is returned.

Syntax

CREATE FUNCTION  function-name (Parameters)  
RETURNS @TableName TABLE  
(Column_1 datatype,  
       .  
       .  
 Column_n datatype  
)  
AS  
BEGIN  
Statement 1  
Statement 2  
      .  
      .  
 Statement n  
 RETURN   
 END