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.
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.
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.
select dbo.fnIsLeapYear(2016)
Output: This is leap year
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.
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:
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.