- A function is a database object in SQL Server.
- It accepts only input parameters, perform an action and return the result.
There are two types of function in SQL Server
- System Functions
- User-Defined Functions
- All the built-in functions supported by the server called System functions.
- Built-In functions are used in SQL SELECT expressions to calculate values and manipulate data like Mathematical Functions, Ranking Functions, String Functions, etc.
First, we create a STUD table and insert dummy data.
Here we retrieve the Student name and birth year using the Datename function. By using Date name function we will retrieve only the birth-year of a student.
If you expand Datename function you will see two-parameter Date part and Expression.
USER DEFINED FUNCTIONS:
- The user-defined function accepts the parameter and returns a result.
Here we create a USER_DEFINE function and by using the SELECT statement retrieve the values in the STUD table.
DOB is passed as a parameter to the USER_DEFINE function which returns the formatted date.
There are two types of User-Defined Functions
- Scalar Function
- Table-valued Function
- It is a function that takes one or more values but returns a single value.
Here we create SCALAR_FUN and retrieve first name and surname as a name in one column also retrieve the gender column.
- A table-valued function is a user-defined function that returns data of a table type.
INLINE TABLE VALUED FUNCTION:
- This function returns a table data type based on a single SELECT Statement.
We create INLINE_FUN and retrieve the data of the STUD table.
MULTI-STATEMENT TABLE VALUED FUNCTION:
- Multi-statement table-valued function returns a table variable as a result of actions performed by the function.
- This function also returns the tabular result set.
- It is unlike the inline table-valued function, It contains only one statement or more than one statement.
Here we create another table that is STUD1.
Then create a MULTIVALUED function.
- Declaring the return table structure.
- It starts and ends with the BEGIN/END block.
- The function body can involve one or more than one statement.
- Must use to RETURN operator.
If you are a newbie to database learning — SQL Server recommended is the following must-watch video: