2 Answers

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


 --Create a table 

 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)
Begin return (Select @FirstName + ' '+ @LastName);
 --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
 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
 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

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