Function in SQL -part 8

 In this blog we are going to understand  Sql function.

 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

1.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.

                                                     

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                                                                        

2.USER DEFINED FUNCTIONS:

•  The user-defined function accepts the parameter and returns a result.
•   User-defined functions are create by a user.

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

1.SCALAR 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 Dob column.

SCALAR FUNCTION

Now see a newly created function in the database.

2.TABLE-VALUED FUNCTION:

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

TABLE-VALUED FUNCTION

There are two types of Table value Functions

1.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.

INLINE TABLE-VALUED FUNCTION

Now see a newly created function in the database.

2.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.                      

            MULTI STATEMENT TABLE-VALUED FUNCTION

                                               

Now see a newly created function in the database.

     

                                                               

For more understanding watch below video :

https://youtu.be/uGlfP9o7kmY

      

Thank You!!!