USER DEFINED FUNCTIONS

SQL Server allows us to create our functions called as user defined functions in SQL Server. 

User defined functions is reusable,Instead of writing same logic again and again , we create a user defined function of it.

There are two types of SQL user defined functions as given below 

1. Scalar Function:  It is a function that always returns a single scalar data value. BEGIN .. END block is used in Scalar function.
2. Table Valued Functions: It is a user defined function in SQL Server that returns a tabular result set. There are two types of Table Valued functions as given below.

• • Inline Table valued Functions: This function returns a tabular result set based on a single Select Statement .
  • Multi-Statement Table valued Functions: This function also returns the tabular result set. But, unlike the inline table valued function, It consists a series of T-SQL statements inside the function body and it has a BEGIN.. END block. 

Scalar Function

SYNTAX

CREATE FUNCTION fn_name (@parameter1 datatype1, @parameter2 datatype2, ...)
RETURNS  datatype 
AS BEGIN
RETURN 
{expression }
END

To get the sum of values using scalar function

Lets create a User defined scalar functions named Fn_CalulateSum that returns the sum of two input values.

CREATE FUNCTION Fn_CalulateSum( @A Int , @B Int)
RETURNS  INT
AS 
BEGIN
RETURN (@A + @B)
END

 

As you can see, the function Output for parameter values 10 & 125, it returns the sum of parameter values 10 and 125 that is = 135.

Select dbo.Fn_CalulateSum (10 ,125) as Get_Sum

Lets look at one more example of SQL scalar function.

To get the sum of unit price for a specific order Id using scalar function

Following Scalar function named Fn_getOrderPrice, returns the total sum of unit price based on input value for OrderId.

CREATE FUNCTION Fn_getOrderPrice( @OrderId Int )
RETURNS  INT
AS
BEGIN
RETURN (Select sum(UnitPrice) as totalprice 
From [Order Details] Where orderId = @OrderId)
END

As you can see the function output, It returns the total Unitprice for order Id = 10248

select dbo.Fn_getOrderPrice (10248) as total_unitPrice

Inline Table Valued Function

SYNTAX

CREATE FUNCTION fn_name
(@parameter1  datatype1,  @parameter2  datatype2 , ...  )
RETURNS TABLE AS RETURN (select .......)

CREATE FUNCTION Fn_getOrderDetails
(@OrderId Int)
RETURNS TABLE 
AS 
RETURN Select * from [Order Details] where orderId =@OrderId 

Here is the output of function , It returns the order details for order Id =10248.

Select * from Fn_getOrderDetails(10248)

Multi-Statement Table Valued Function

SYNTAX

CREATE FUNCTION fn_name( @parameter1  datatype1,  @parameter2  datatype2 , ...  )
RETURNS @returntablename TABLE
(column1 datatype 1, column2  datatype2, .....)
AS
BEGIN
INSERT INTO @returntablename   SELECT .....
RETURN
END

Following Multi-statement Table Valued function returns  OrderId, Unitprice and Quantity from table [Order details]  for a specific order id also deletes the records from output for which quantity value is <=5. 

CREATE FUNCTION fn_Orderdata(@OrderId Int)
RETURNS @ordrtbl TABLE(OrdrId int, UnitPrice numeric(18,2), Quantity numeric(18,2))
AS
BEGIN
INSERT INTO @ordrtbl
SELECT orderId ,unitPrice ,quantity from [Order Details] WHERE orderId =@OrderId
Delete from @ordrtbl where Quantity <=5;
RETURN
END

Here is the output of Multi-satement table valued function for Order Id =1028, also you can see, there is no qunatity which is less then equal to 5.

select * from fn_Orderdata (10248)

Difference Between Stored Procedure and User Defined Functions