User Defined Functions in SQL

Why Use User-Defined Functions (UDFs)?

User-defined functions offer several benefits in SQL:

1. Modular Programming: User-defined functions allow us to create reusable code blocks that can be stored in the database. This modular approach enables us to call the function from any program multiple times without impacting the program's source code.
2. Improved Performance: By using user-defined functions, we can leverage caching techniques. Since the same function is used repeatedly, intermediate results can be stored, optimizing the computation process and resulting in faster execution.
3. Reduced Network Traffic: User-defined functions can help reduce network traffic by allowing us to express complex constraints that cannot be easily expressed in a single monomial expression. This enables us to control the amount of data displayed or sent to the user, reducing unnecessary network transmission.

Types of User-Defined Function in SQL

There are three types of user-defined functions in SQL:

1. Scalar Functions: Scalar functions, also known as User-Defined Scalar Functions, are functions that return a single value of a specific data type. These functions are defined in the RETURNS clause. Scalar functions can be categorized as inline or multi-statement functions.

   • Inline Scalar Functions: An inline scalar function implies that the returned scalar value is the result of a single statement. It is a concise and efficient way to define a scalar function that performs a calculation or manipulation on input parameters and returns a single value.
   • Multi-Statement Scalar Functions: A multi-statement scalar function indicates that the returned scalar value is derived from a series of statements. This type of scalar function allows for more complex logic and calculations, utilizing multiple statements to compute the final scalar value.

2. Table-Valued Functions: Table-Valued Functions, also referred to as User-Defined Table-Valued Functions, are functions that return a table as their result. The output of these functions is typically in the form of a table, similar to the result of a SELECT query. Table-Valued Functions offer the ability to encapsulate complex queries or calculations and return the results as a table. This allows us to treat the function result as a table and perform further operations such as filtering, joining, or aggregating the data.

3. System Functions: We could use the SQL built-in system functions to perform a variety of operations. While system functions provide convenience and versatility, one disadvantage is that they cannot be modified or customized according to specific user needs. These functions are predefined by the database management system and their behavior and output are fixed.

User-Defined Function Returning the Table

In SQL, when creating user-defined functions, we have the capability to design them to return a table as the result. These functions can accept specific parameters, allowing us to generate output that meets specific conditions or criteria. To illustrate this concept, let's take a look at a coding example that will provide a clearer understanding.

Code:

Explanation:

• The above SQL code demonstrates the creation of a user-defined function called new_function. This function takes a decimal parameter @variable with a precision of $9$ and scale of $6$.
• The function is defined to return a table as its result using the RETURNS TABLE syntax. The AS RETURN keyword signifies the beginning of the function's definition.
• Inside the function, a SELECT statement is used to retrieve data from a table named new_table. The SELECT * statement retrieves all columns from the new_table.
• The WHERE clause is applied to the new_table, filtering the rows based on a condition. In this case, the condition is place.variable > @variable, which means only rows where the value of the variable column in the place table is greater than the input parameter @variable will be included in the function's result.
• Once the function is created, it can be used in SQL queries like a regular table. It accepts the parameter @variable and returns the filtered rows from the new_table that satisfy the specified condition.

Valid Statements in a Function

There are various valid statements that can be used in a function. They are as follows:

1. DECLARE Statement: This statement is used to declare local data variables, triggers, and cursors within a user-defined function or procedure.
2. SET Statement: It is used to assign values to local objects within a user-defined function. These objects can be either scalar or table local variables.
3. FETCH Statements: These statements can assign values to local variables using the INTO clause.
4. TRY..CATCH Statement: This statement is used to control the flow of the program and handle exceptions.
5. SELECT Statement: These statements are used to assign values to variables within the function.
6. UPDATE, INSERT, DELETE Statement: These statements are used to modify local table variables within the user-defined function.
7. EXECUTE Statement: This statement is used to call stored procedures or other user-defined functions.

Schema Bound Functions

It is a very useful function present in SQL and used at the time of function creation with the help of the "SCHEMABINDING" clause. This function assists us in defining or binding the schema of the object referenced in the function. However, it is important to note that the objects referenced by this function cannot be modified or deleted.

Certain conditions need to be fulfilled to apply this technique:

• All views and user-defined functions related to the function must be schema-bound.
• All objects referenced by the function must be stored in the database, and one-part or two-part names must be used to refer to these items.
• All objects, such as tables and user-defined functions, used in the function must have the necessary REFERENCES permission.