Functions in SQL Server

FUNCTION:

• 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

SYSTEM 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.
• Example:-

First, we create a STUD table and insert dummy data.

STUD TABLE

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.

SYSTEM FUNCTION

If you expand Datename function you will see two-parameter Date part and Expression.

BUILT-IN FUNCTIONS

USER DEFINED FUNCTIONS:

• The user-defined function accepts the parameter and returns a result.
• Example:-

Here we create a USER_DEFINE function and by using the SELECT statement retrieve the values in the STUD table.

USER DEFINED FUNCTION

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

SCALAR FUNCTION:

• It is a function that takes one or more values but returns a single value.
• Example:-

Here we create SCALAR_FUN and retrieve first name and surname as a name in one column also retrieve the gender column.

SCALAR FUNCTION

TABLE-VALUED FUNCTION:

• A table-valued function is a user-defined function that returns data of a table type.

TABLE-VALUED FUNCTION

INLINE TABLE VALUED FUNCTION:

• This function returns a table data type based on a single SELECT Statement.
• Example:-

We create INLINE_FUN and retrieve the data of the STUD table.

INLINE TABLE-VALUED FUNCTION

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.
• Example:-

Here we create another table that is STUD1.

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.

MULTI STATEMENT TABLE-VALUED FUNCTION

If you are a newbie to database learning — SQL Server recommended is the following must-watch video: