What Are SQL Functions

0
3544

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

  4. Table Value Function :
    • Table value function are the another type of Sql function. It return table as a result set.
    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)

    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