Skip to content
Home » USER DEFINED FUNCTIONS

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, you can create a user defined function of it.

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

  1. Scalar Function: This function 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 BEGINRETURN{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  INTASBEGINRETURN (@A + @B)END

 

As you can see, the output of function for input parameter 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 another example of 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  INTASBEGINRETURN (Select sum(UnitPrice) as totalpriceFrom [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 .......)

To get the Order details for a specific order Id using  Inline Table valued function

Following Inline table valued functions named Fn_getOrderDetails, returns the details for order based on input order id value.

CREATE FUNCTION Fn_getOrderDetails(@OrderId Int)RETURNS TABLEASRETURN 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, .....)ASBEGININSERT INTO @returntablename   SELECT .....RETURNEND

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))ASBEGININSERT INTO @ordrtblSELECT orderId ,unitPrice ,quantity from [Order Details] WHERE orderId =@OrderIdDelete from @ordrtbl where Quantity <=5;RETURNEND

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

 4,550 total views,  2 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.