How to Create Scalar, Inline and Multi-Statement Table Valued User Defined Functions in SQL Server

What are different Types of User Defined Functions and How to Create them in SQL Server
The User defined functions are created by user in system database or in user defined database. There are three types of user defined functions.
1. Scalar Function
2. Inline Table-Valued Function
3. Multi-statement Table-Valued Function

Let us go through each of these with examples.

1) Scalar User-Defined Function :

A Scalar user defined function returns a single value as a result of actions perform by function. A Scalar UDF can accept 0 to many input parameter and will return a single value. A Scalar user-defined function returns one of the scalar (int, char, varchar etc) data types, but the Text, ntext, image and timestamp data types are not supported.

Example 1 : 

Scalar UDF to get the Region Name by Passing the Count Name as a Parameter.

Suppose we Region Dimension Table as follows...

SELECT [Region_Id]

      ,[Region_Code]

      ,[Region_Name]

      ,[Country]

  FROM [dbo].[DimRegion]

GO

On this Table we will create a Scalar UDF to get the Region Name by Passing the Count Name as a Parameter.

Create Function [dbo].[udf_GetRegionOfCountry]

                      ( 

                     @pCountry as Varchar(50)

                       )

Returns Varchar(50)

As

Begin

           Return (    Select Region_Name From [dbo].[DimRegion]

                        Where Country=@pCountry   )

End

GO

Print [dbo].[udf_GetRegionOfCountry]('France') 

Select [dbo].[udf_GetRegionOfCountry]('France') as RegionName

--Drop Function [dbo].[udf_GetRegionOfCountry]

Result :

RegionName
Europe

Example 2 : 
Scalar UDF to get the Count of Employees by Passing the Department Name as a Parameter.

Suppose we have an Employee Table as follows..

SELECT [Emp_Id], [Emp_Name], [Gender], [Job_Id], [Dept_Id], [Joining_Date], [Basic_Sal],                [Dept_Name] FROM [dbo].[Emp_Details]
GO

On this Table we will create a Scalar UDF to get the Count of Employees by Passing the Department Name as a Parameter.
Create Function [dbo].[udf_EmpCountByDept]
(

@pDeptName Varchar(50)

) Returns Int

As

Begin

Return (

 Select Count(Emp_Id) as Emp_Count 

 from [dbo].[Emp_Details]

Where Dept_Name=@pDeptName

    )

End

GO

Print [dbo].[udf_EmpCountByDept]('BI&A')

Select [dbo].[udf_EmpCountByDept]('BI&A') Emp_Count

--Drop Function [dbo].[udf_EmpCountByDept]

Result :

Emp_Count

6

2) Inline Table-Valued Function:

User defined inline table-valued function returns a table variable as a result of actions perform by function. 

An inline table-valued function returns a variable of data type table whose value is derived from a single SELECT statement. Since the return value is derived from the SELECT statement, there is no BEGIN/END block needed in the CREATE FUNCTION statement. 

There is also no need to specify the table variable name (or column definitions for the table variable) because the structure of the returned value is generated from the columns that compose the SELECT statement. Because the results are a function of the columns referenced in the SELECT, no duplicate column names are allowed and all derived columns must have an associated alias.

Example : 
Inline Table-Valued Function to get the Employee Details of Specific Department by Passing the Department Name as a Parameter.
Create Function [dbo].[udf_EmpDtlsByDept]

(

@pDeptName Varchar(50)

) Returns Table

AS

Return (

Select * from [dbo].[Emp_Details]

Where Dept_Name=@pDeptName

    )

GO

Select * From [dbo].[udf_EmpDtlsByDept]('BI&A')

Result :

3) Multi-Statement Table-Valued Function :

User defined multi-statement table-valued function returns a table variable as a result of actions perform by function.

A Multi-Statement Table-Valued user-defined function returns a table. It can have one or more than one T-SQL statement. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, we can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

Example : 
Multi-Statement Table-Valued Function to get the Sales Details of Specific Region by Passing the Region Name as a Parameter, then those results will be Inserting into a Table Variable (  @vSalesByRegion ).

Suppose we have a Sales Table and its related Region Table as follows...

Select * From [dbo].[Tbl_Sales]

Select * From [dbo].[Tbl_Region]

Now based on the Region Id, we will Join both the Tables to Retrieve the Sales by Region and Country wise.

Once we retrieve that result set , next we apply the filter by Region Name which is passing in our function.

Finally, the result data set will stored in to our Table variable @vSalesByRegion .

CREATE FUNCTION [dbo].[udf_SalesByCustRegion]

( 

   @pRegionName Varchar(50)

)

RETURNS @vSalesByRegion Table

(

     CountryName Varchar (50),

GrossSales Float

)

 AS

 BEGIN

INSERT INTO @vSalesByRegion

SELECT S1.[Country],Sum([Gross_Sales]) [Gross_Sales]

FROM(

SELECT

   R.[Region_Name]

  ,R.[Country] 

  ,[Units_Sold]

  ,[Gross_Sales]

FROM [dbo].[Tbl_Sales] S 

INNER JOIN

[dbo].[Tbl_Region] R  ON S.[SalesRegion_Id]=R.[Region_Id]

WHERE R.[Region_Name]=@pRegionName

) S1 

GROUP BY S1.[Country]

IF @@ROWCOUNT = 0

BEGIN

INSERT INTO @vSalesByRegion

VALUES ('No Country Found',0)

END

RETURN

END

GO

Select * From [udf_SalesByCustRegion]('Asia')

Result :

CountryName GrossSales

India          7842800

Japan          6167200

Select * From [udf_SalesByCustRegion]('Dummy')

Result :

CountryName              GrossSales

No Country Found            0

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------