Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

home / developersection / forums / what is the role of functions in sql server ?
Ask Question
Anonymous User
Anonymous User

A_Philosopher

I am a content writter !

Message

2 Answers

Anonymous User
Anonymous User 14 Jun 2019
Report Abuse

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. 

Anonymous User
Anonymous User 14 Jun 2019
Report Abuse

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