Functions in SQL Server

Functions in SQL Server

Introduction
Functions in SQL Server are reusable blocks of code that accept parameters and return a single value or a table of values. They are similar to stored procedures but differ in the way they are used. Functions can be categorized into two types: Scalar functions and Table-valued functions.

Scalar Functions

Scalar functions return a single value, such as an integer, string, or date, based on the input parameters. They are commonly used in SQL Server to perform calculations on data.

Syntax:

sql
CREATE FUNCTION function_name (@parameter1 data_type, @parameter2 data_type, ...)
RETURNS return_data_type
AS
BEGIN
    -- Function logic here
    RETURN value;
END;

Example:
Let's create a scalar function that calculates the total price (unit price multiplied by quantity) for a given product.

sql
CREATE FUNCTION CalculateTotalPrice (@productID INT, @quantity INT)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @totalPrice DECIMAL(10, 2);

    SELECT @totalPrice = UnitPrice * @quantity
    FROM Products
    WHERE ProductID = @productID;

    RETURN @totalPrice;
END;

Explanation:

• We define a function named CalculateTotalPrice that takes two parameters: @productID and @quantity.
• Inside the function, we declare a variable @totalPrice to hold the result.
• The function performs a SELECT query to fetch the UnitPrice of the specified product from the Products table and calculates the total price by multiplying it with the given @quantity.
• Finally, the function returns the calculated @totalPrice.

Table-valued Functions

Table-valued functions return a table as their output, which means they can be used in the FROM clause of a SELECT statement and treated like a regular table.

Syntax:

sql
CREATE FUNCTION function_name (@parameter1 data_type, @parameter2 data_type, ...)
RETURNS TABLE
AS
RETURN (
    -- Query to generate the table
);

Example:
Let's create a table-valued function that returns the list of products with a quantity greater than the given input.

sql
CREATE FUNCTION GetProductsByQuantity (@quantityThreshold INT)
RETURNS TABLE
AS
RETURN (
    SELECT ProductID, ProductName, Quantity
    FROM Products
    WHERE Quantity > @quantityThreshold
);

Explanation:

• We define a function named GetProductsByQuantity that takes a single parameter @quantityThreshold.
• The function returns a table containing columns ProductID, ProductName, and Quantity.
• The function selects rows from the Products table where the Quantity is greater than the specified @quantityThreshold.
• The result is returned as a table from the function.

Conclusion

Functions in SQL Server provide a powerful way to encapsulate logic and calculations, promoting code reusability and maintainability. By understanding the different types of functions and their usage, developers can efficiently perform data manipulations and enhance the performance of their SQL queries.