role of Functions in SQL server
Last updated:6/14/2019 5:28:12 AM
role of Functions in SQL server
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.
© Copyright © 2010 - 2021 MindStick Software Pvt. Ltd. All Rights Reserved
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.