Functions -- Sql Server

   Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, and Delete records in the database table.

System Defined Function

    These functions are defined by Sql Server for different purpose. We have two types of system defined function in Sql Server

 Scalar Function

    Scalar functions operate on a single value and returns a single value. Below is the list of some useful Sql Server Scalar functions.

Differnet Types of Scalar Functions

•  Abs (-10.67): This returns absolute number of the given number means 10.67. 
•  Rand (10):This will generate random number of 10 characters.
•  Round (17.56719,3):This will round off the given number to 3 places of decimal means 17.567.
•  Upper ('dot net') :This will returns upper case of given string means 'DOTNET'.
•  Lower ('DOT NET'):This will returns lower case of given string means 'dot net.
•  Ltrim (' dot net') : This will remove the spaces from left hand side of 'dot net' string.  
• Convert (int, 15.56):This will convert the given float value to integer means 15.

Aggregate Functions (Transact-SQL)

       Aggregate functions perform a calculation on a set of values and return a single value. Except for COUNT, aggregate functions ignore null values. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.

All aggregate functions are deterministic. This means aggregate functions return the same value any time that they are called by using a specific set of input values. For more information about function determinism

The OVER clause may follow all aggregate functions except GROUPING and GROUPING_ID.

Aggregate functions can be used as expressions only in the following:

• The select list of a SELECT statement (either a subquery or an outer query).
• A HAVING clause.

Transact-SQL provides the following aggregate functions:

AVG	MIN
CHECKSUM_AGG	SUM
COUNT	STDEV
COUNT_BIG	STDEVP
GROUPING	VAR
GROUPING_ID	VARP
MAX

Important Points about Functions: 

•  Unlike Stored Procedure, Function returns only single value.
•  Unlike Stored Procedure, Function accepts only input parameters.
•  Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in database table(s).
• Like Stored Procedure, Function can be nested up to 32 level.
• User Defined Function can have upto 1023 input parameters while a Stored Procedure can have upto 2100 input parameters.
• User Defined Function can't returns XML Data Type.
• User Defined Function doesn't support Exception handling.
•  User Defined Function can call only Extended Stored Procedure.
•  User Defined Function doesn't support set options like set ROWCOUNT etc.

User Defined Function:

These functions are created by user in system database or in user defined database. We three types of user defined functions. 

Scalar Function

User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.
      

     for an instance for Scalar Function
User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.

Create a table

 CREATE TABLE Employee
              ( 
               EmpID int PRIMARY KEY,  

  FirstName varchar(50) NULL, 

 LastName varchar(50) NULL,

 Salary int NULL, 

 Address varchar(100) NULL,

  )

Insert Data 

    Insert into Employee(EmpID,FirstName,LastName,Salary,Address)   Values(1,'Omkar','Marmamula',22000,'Delhi'); 

 Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(2,'Ali','Khan',15000,'Delhi');

Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(3,'Bhunvna','Shakya',19000,'Noida');

Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(4,'Deepak','Kumar',19000,'Noida')

Creating Function

 Create function fnGetEmpFullName

 (

     @FirstName varchar(50),

     @LastName varchar(50)

 )

 returns varchar(101)

 As

 Begin return (Select @FirstName + ' '+ @LastName);

 end

Calling Function

Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee

 Inline Table-Valued Function:

      User defined inline table-valued function returns a table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement.

Creating Function: 
 

Create function fnGetEmployee()

                returns Table

 As

return (Select * from Employee)

 Calling Function:

              Select * from fnGetEmployee()   

Multi-Statement Table-Valued Function:
    
      User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.
  
          Create function fnGetMulEmployee(
          returns @Emp Table 
                    ( 
                              EmpID int,  
                               FirstName varchar(50),

Salary int

           )
            As
                      begin
          Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;
           update @Emp set Salary=25000 where EmpID=1; 

return

 end  

 --Now call the above created function 

 Select * from fnGetMulEmployee()

Diversities between Functions and Stored Procedures