User Defined Functions (UDF) in SQL

User Defined Functions (UDFs) are a powerful feature in SQL. They allow developers to encapsulate a sequence of operations into a single, reusable function. UDFs can accept zero, one, or multiple parameters and can return either a single scalar value or a table data type. This makes them particularly useful for operations that are frequently performed but not built into the SQL language itself.

UDF Example

Imagine you have a database that contains information about products, and you often need to calculate the total price of a product by multiplying its unit price with the quantity. Instead of writing this calculation every time, you can define a UDF to do this for you.

                            CREATE FUNCTION dbo.CalculateTotalPrice (@unitPrice DECIMAL(10,2), @quantity INT)
                            RETURNS DECIMAL(10,2)
                            AS
                            BEGIN
                                RETURN @unitPrice * @quantity
                            END;
                        

The above UDF, named CalculateTotalPrice, accepts two parameters: a unit price and a quantity. It returns the total price as a decimal value. Once this UDF is defined, you can call it in your SQL queries like so:

                            SELECT ProductName, dbo.CalculateTotalPrice(UnitPrice, Quantity) AS TotalPrice
                            FROM Products;
                        

This would give you a list of product names alongside their total prices, calculated using the UDF.

UDFs are just one of the many powerful features in SQL that enable developers to create more modular and maintainable code.