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__:

**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
**User Defined Function (UDF) :**it return result set in a single value**Table Value Function (TVF) :**it return data in row and column form or in a table- Before creating function we must need to understand the basic structure of a sql function.
**Structure of SQL Function :**- Sql function must use following piece of information that is used to describe what a function does.
- Name
- Parameter
- Return type
**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
**Table Value Function :**- Table value function are the another type of Sql function. It return table as a result set.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> ( -- Add the parameters for the function here <@Param1, sysname, @p1> <Data_Type_For_Param1, , int> ) RETURNS <Function_Data_Type, ,int> AS BEGIN -- Declare the return variable here DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int> -- Add the T-SQL statements to compute the return value here SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1> -- Return the result of the function RETURN <@ResultVar, sysname, @Result> END |

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

1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE FUNCTION [dbo].[fnIsLeapYear] (@Year int) RETURNS varchar(50) AS BEGIN DECLARE @Result varchar(50) SET @Result = CASE WHEN (@Year % 4 = 0 AND @Year % 100 != 0) OR @Year % 400 = 0 THEN 'This is leap year' ELSE 'This is NOT a leap year' END RETURN @Result END |

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.

1 |
select dbo.fnIsLeapYear(2016) |

**Output:** This is leap year

1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> ( -- Add the parameters for the function here <@param1, sysname, @p1> <Data_Type_For_Param1, , int>, <@param2, sysname, @p2> <Data_Type_For_Param2, , char> ) RETURNS TABLE AS RETURN ( -- Add the SELECT statement with parameter references here SELECT 0 ) GO |

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
create FUNCTION [dbo].[fnlist_leapyear](@fromyear int, @Toyear int) RETURNS @leapyear TABLE ( yearValue int NOT NULL, remarks varchar(50) ) AS BEGIN WHILE (@fromyear <= @Toyear) BEGIN INSERT INTO @leapyear (yearValue,remarks) values ( @fromyear, CASE WHEN (@fromyear % 4 = 0 AND @fromyear % 100 != 0)OR @fromyear % 400 = 0 THEN 'This is leap year' ELSE 'The is NOT a leap year' END ) SET @fromyear = @fromyear +1 END; RETURN; end; |

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:**

1 |
select * from dbo.fnlist_leapyear(2012,2016) |

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.