What Are SQL Functions

0
2754

In this article we will learn about SQL functions, structure of the Sql functions, how to implement user defined function and table value function along with suitable examples.

To understand what are Sql functions and its implementations, follow the points given below:

  1. Introduction:
    • Sql Function performs action according to requirement.
    • A function is piece of code having return behaviour in it. It returns appropriate value on function call.
    • It avoids repeating piece of code in multiple places.
    • For example we can use SQL function, if we need same piece of code in multiple places.
    • In this article We will learn following type of SQL Function.
    • It basically allows wrapping up piece of code and then return result set as single value or in table value
      1. User Defined Function (UDF) : it return result set in a single value
      2. Table Value Function (TVF) : it return data in row and column form or in a table

      diagram 1

    • Before creating function we must need to understand the basic structure of a sql function.

  2. Structure of SQL Function :
    • Sql function must use following piece of information that is used to describe what a function does.
      1. Name
      2. Parameter
      3. Return type

  3. User Defined Function:
    • A function defined by user is called as user defined function (UDF).
    • It returns the result set in single value
    • User defined functions are the requirements defined by user

    Example: In this example I have used the function to find leap year from given year. I have wrapped the formula in following function and I will use these in multiple places whenever I need similar requirement.

    So far I have created my own user defined function that tells me if a given year is a leap year or not.
    Now to execute this function we need to pass year in it.

    Output: This is leap year

  4. Table Value Function :
    • Table value function are the another type of Sql function. It return table as a result set.

    Example: In following example a function shows the list of leap year/ not a leap year from given year range, start year and end year.

    So now our table value function is ready. This will give result as a table. This function gives list of year with remarks that year is leap year/not a leap year from given range start year or end year.

    Execute the function by following query:


    output

Thus, we have learnt about SQL functions, structure of the Sql functions, how to implement user defined function and table value function along with suitable examples.

LEAVE A REPLY

Please enter your comment!
Please enter your name here