There are two types of functions in SQL: built-in functions and user-defined functions. Built-in functions are provided in the default installation of a SQL server for immediate use. Table-valued functions or user-defined functions are created by users for their ease, especially for long query statements that are used several times in the application.
In this article, we will learn how to create a user-defined function in SQL and how to call these functions within our queries. We will be using Microsoft SQL Server in this article as our database.
Built-in (Server) Functions in SQL
Built-in functions, also known as server functions, are functions provided by the SQL database that is readily available for use. These are commonly used functions that have been widely used, so the database has these implemented in an optimized and effective manner.
If you are using SQL Server Management Studio, we can view all the server functions which are stored in different folders inside the system functions folder.
Examples of server functions are Date and Time functions, statistical functions such as Sum, Average, Maximum, Minimum, etc. There are two sub-types of built-in or server functions in SQL: the scalar functions and aggregate functions.
These are the functions in SQL which take in only a single parameter and returns a single value as a result. Examples of these functions are the ROUND function to round off numbers, RAND function to generate a random number, UPPER and LOWER function to change the capitalization of strings, etc.
This is a type of server function provided by SQL that performs an operation on the specified group of data within a table and returns only one value as a result. Examples of these functions are the MAX and MIN function to get the highest and lowest values, AVE function to get the average value, and the COUNT function to count the number of rows.
Table-valued Functions in SQL
User-defined functions or table-valued functions are functions that return data from a table. We can use table-valued functions as we use a table because the return type of these functions is a table.
Creating table-valued Functions
To create a table-valued function, the following syntax is used:
CREATE FUNCTION FunctionName(parameter1, parameter2, ...) RETURNS ... AS ...
The function takes in the names of the parameter that are needed in the result table, the name of the table from which data will be selected, and the condition according to which the data will be selected. An example is as follows:
Create Function Query in SQL
In this function, we want to return the Product Name, Year of the Model, and the Listed Price in the resulting table, based on a specific Year of the Model. We can see after the RETURN statement that we include a SELECT query to return the attributes (fields), followed by a WHERE clause to filter the model_year.
After executing this statement, the function is now ready to be used in queries.
Executing table-valued Functions
When we create any function, this is added to the list of functions that we can see in tools depending on the database used.
For MySQL, we can see this in the Functions tab of MySQL Workbench.
In Microsoft SQL Server, this is located in the Programmability tab in SQL Server Management Studio.
To execute the function. We need to call that function in a query, and then execute it in the same way as we use built-in or server functions.
Using a table-valued function in a query.
The sample result set when executing the function.
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.