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.
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.
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.
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
