User-Defined Functions in SQL Server

SQL Server has two types function system or built-in function and user-defined function. In today’s article, we shall cover User Defined Functions (UDF).

UDFs are the custom functions that developers need to create. SQL Server supports two types of User-Defined Functions.

• Table-Valued Functions
• Scalar Valued Functions

Now, let’s look into how to create them.

To create User-Defined Functions, let’s consider the table.

Table-Valued Function

When we run a function and return a table as a result, then it’s called a table-valued function.

Syntax

CREATE FUNCTION [dbo].[Function_Name]()
RETURNS TABLE
AS
RETURN (
    Statement
)

Now, let's create a Table-Valued function.

CREATE FUNCTION [dbo].[udf_ProductDetailsQuantitywise] (@product_quantity INT)
RETURNS TABLE
AS
RETURN (
    SELECT * 
    FROM Products 
    WHERE product_quantity >= @product_quantity
);

Below is the Output.

As we can see in the above screenshot, the Table-Valued function is created, we took input parameter as @product_quantity which will return table as a result.

Now, let’s call the function [dbo].[udf_ProductDetailsQuantitywise] and pass the value as 12 for the input parameter.

SELECT * FROM [dbo].[udf_ProductDetailsQuantitywise] (12)

We can see [dbo].[udf_ProductDetailsQuantitywise] function is used to get the quantity-wise product table. Hence, the table-valued function is useful for returning the table based on business logic.

Scaler Valued Function

Unlike the table-valued function, this function returns a single scaler value based on the input parameter we passed.

Syntax

CREATE FUNCTION [dbo].[Function_Name] (ParameterList)
RETURNS DataType
AS
BEGIN
    RETURN Return_DataType
END

Now, let's create a Scaler-Valued function.

CREATE FUNCTION [dbo].[udf_ProductPriceTotal] 
    (@unit_price INT, @product_quantity INT)
RETURNS INT
AS 
BEGIN
    RETURN @unit_price * @product_quantity
END

Below is the Output.

As we can see in the above screenshot, scaler-valued function is created, it took two input parameters @unit_price and @product_quantity which will return a single scaler value as a result.

Now, let’s call the function using [dbo].[udf_ProductPriceTotal].

SELECT 
    product_name, 
    unit_price, 
    product_quantity, 
    [dbo].[udf_ProductPriceTotal](unit_price, product_quantity) AS TotalPrice
FROM Products;

We can see the udf_ProductPriceTotal function is used to get the total price of each product in the table. Hence, the scaler function is useful for returning the single scaler value.

Summary

UDFs are a great way to customize the function based on specific needs. In the article, we covered how to create them by taking simple examples. I hope you liked the article, and I am looking forward to your comments and suggestions.