In SQL server there are three kinds of User-defined functions

1. Scalar functions
2. Table Valued functions
   1. Inline table valued functions
   2. Multi-statement table-valued functions

Scalar function

Scalar function is a function which may or may not take any input parameter but always return a single value. The Scalar function may return any kind of datatype except text, ntext, image, cursor and timestamp.

Syntax for creating Scalar Function.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION  
(
	-- Add the parameters for the function here
	 
)
RETURNS 
AS
BEGIN
	-- Declare the return variable here
	DECLARE  

	-- Add the T-SQL statements to compute the return value here
	SELECT  = 

	-- Return the result of the function
	RETURN 

END
GO

Now we have a table named order which has following records

So now i want to calculate the number of orders created by particular CustomerId by using Scalar function

Calling function in a Select query.

SELECT DISTINCT CUSTOMERID,DBO.GetOrderCount(CUSTOMERID) AS ORDER_COUNT FROM    [Order]

Inline table valued function

As in Scalar function we learn that we can return a single value so incase where we want to return the multple value from the function we can make user of Inline table valued function which returns a table data type.

Scenario:

We have a below table:

Now we want to return the records based on the passed customer id to the Inline table valued function.

Syntax:

-- ================================================
-- Template generated from Template Explorer using:
CREATE FUNCTION fn_GetOrdersByCustomerId
(	
	-- Add the parameters for the function here
	@CustomerId nvarchar(100)
)
RETURNS TABLE 
AS
RETURN 
(
	
SELECT  CUSTOMERID,STOREID,GROSSTOTAL, ORDERCREATEDDATE
 from [Order] where CUSTOMERID=@CustomerId
)
GO

In Inline table valued function we don’t use Begin and End block as we did in Scalar function.

Once we compile the table valued function the function is located in Table-Valued function section under Programmatically-functions. This function will return the CustomerId, StoreId, GrossTotal, OrderCreatedDate from Order table based on the CustomerId passed to the function as shown below:

Executing the inline table valued function

select * from dbo. fn_GetOrdersByCustomerId('123456789') 

Output:

Multi Statement table value Functions

Multi statement table valued function is quiet similar to Inline table valued function.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: 
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
	-- Add the parameters for the function here
	<@param1, sysname, @p1> <data_type_for_param1, , int>, 
	<@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
	-- Add the column definitions for the TABLE variable here
	<Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
	<Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	
	RETURN 
END
GO

Now we can see the syntax different between Inline table valued function and Multi-statement Inline function. In Multi-Statement we return a table with structure defined after the Table data type.

Using Multi-Statement Inline Function to retrieve records from the Order table based on storeId passed.

create function fn_GetOrders(@StoreId nvarchar(20))
RETURNS @Table Table (ID int,CUSTOMERID nvarchar(200),STOREID nvarchar(20))
AS
BEGIN
INSERT INTO @Table
SELECT ID,CUSTOMERID,STOREID FROM [ORDER] WHERE STOREID=@StoreId;
return

END

select * from fn_GetOrders('3267')

Happy Learning.