What is the role of Functions in SQL server ?

Sanat Shukla

A (SQL) function is a database object in SQL Server. Primarily, this is a set of SQL statements that accept only input parameters, perform actions and return the result. The function can return an only a single value or a table. We can’t use a function to Insert, Update, Delete records in the database tables.

• Unlike Stored Procedure, Function returns an only single value.
•  Unlike Stored Procedure, Function accepts only input parameters.
•  Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in a database table(s).
•  Like Stored Procedure, Function can be nested up to 32 levels.
•  User Defined Function can have up to 1023 input parameters while a Stored Procedure can have up to 2100 input parameters.
•  User Defined Function can't return 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.

Sanat Shukla

EXAMPLE:-

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

 --Create function to get employees

Create function fnGetEmployee()

returns Table

As

 return (Select * from Employee) 



 --Now call the above created function


Select * from fnGetEmployee() 

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

For practice only.