What is the role of Functions in SQL server ?

Total Post:87

Points:615
 228  View(s)
Ratings:
Rate this:

role of Functions in SQL server

  1. Post:87

    Points:615
    Re: What is the role of Functions in SQL server ?

    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.

  1. Post:87

    Points:615
    Re: What is the 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. 

Answer

Please check, If you want to make this post sponsored

You are not a Sponsored Member. Click Here to Subscribe the Membership.