Sql Server Functions

Function 

Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input arameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s).Generally two types of functions are available in sql server these are System Defined Function , another is User defined Funtion. 

Types of Function

1. System Defined Function

SDF are built in function in Sql Server. User defined function are two types are Scalar Function and Aggregate Function.These functions are defined by Sql Server for different purpose.

                  01.Scalar Function

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

Scalar Function

Description

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('dotnet')

This will returns upper case of given string means 'DOTNET'

lower('DOTNET')

This will returns lower case of given string means 'dotnet'

ltrim(' dotnet')

This will remove the spaces from left hand side of 'dotnet' string.

convert(int, 15.56)

This will convert the given float value to integer means 15.

         02. Aggregate Function

                            Aggregate functions operates on a collection of values and returns a single value. Below is the list of some                             useful Sql Server Aggregate functions.

System Aggregate Function

Aggregate Function

Description

max()

This returns maximum value from a collection of values.

min()

This returns minimum value from a collection of values.

avg()

This returns average of all values in a collection.

count()

This returns no of counts from a collection of values.

02. User Defined Function

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

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

1. 
    --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,'Mohan','Chauahn',22000,'Delhi');
   Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(2,'Asif','Khan',15000,'Delhi');
   Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(3,'Bhuvnesh','Shakya',19000,'Noida');
   Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(4,'Deepak','Kumar',19000,'Noida');
   --See created table
   Select * from Employee 
   
   

 --Create function to get emp full name 

Create function fnGetEmpFullName

(

@FirstName varchar(50),

@LastName varchar(50)

)

returns varchar(101)

As

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

end

 --Calling the above created function
Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee 

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

 --Create function to get employees
Create function fnGetEmployee()
returns Table
As
 return (Select * from Employee) 

 --Now call the above created function
Select * from fnGetEmployee() 

              03. 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 for EmpID,FirstName and Salary of Employee
Create function fnGetMulEmployee()
returns @Emp Table
(
EmpID int, 
FirstName varchar(50),
Salary int
)
As
begin
 Insert @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;
--Now update salary of first employee
 update @Emp set Salary=25000 where EmpID=1;
--It will update only in @Emp table not in Original Employee table
return
end 

 --Now call the above created function
Select * from fnGetMulEmployee() 

 --Now see the original table. This is not affected by above function update command
Select * from Employee 

Note

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

Summary

In this article I try to explain the types of function in sql server with example. I hope after reading this article you will be aware about function in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

Refferences: Special thanks to Shailendra Chauhan

http://www.dotnet-tricks.com/Tutorial/sqlserver/KY3T010412-Different-Types-of-SQL-Server-Functions.html