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

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. 

Answer